Hive SQL 速查

CREATE DATABASE [IF NOT EXISTS] db_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[MANAGEDLOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value,...)];

SHOW DATABASES [LIKE pattern];

SHOW DATABASES LIKE 'a%';

DESCRIBE DATABASE [EXTENDED] db_name;

DESCRIBE DATABASE EXTENDED analytics;

USE db_name;

USE analytics;

ALTER DATABASE db_name SET DBPROPERTIES (property_name=property_value,...);
ALTER DATABASE db_name SET OWNER user;
ALTER DATABASE db_name SET LOCATION hdfs_path;
ALTER DATABASE db_name SET MANAGEDLOCATION hdfs_path;

ALTER DATABASE analytics SET DBPROPERTIES (

ALTER DATABASE analytics SET LOCATION '/data/hive/new/analytics.db';

DROP DATABASE [IF EXISTS] db_name [RESTRICT|CASCADE];

DROP DATABASE IF EXISTS analytics;

CREATE DATABASE IF NOT EXISTS chapter4;

CREATE DATABASE IF NOT EXISTS chapter5;
STEP 01

CREATE DATABASE IF NOT EXISTS itcast_ods;
STEP 02

CREATE DATABASE IF NOT EXISTS itcast_dwd;

CREATE DATABASE IF NOT EXISTS itcast_dws;

CREATE VIEW [IF NOT EXISTS] [db_name.]view_name
[(column_name [COMMENT column_comment], ...)]
[COMMENT view_comment]
[TBLPROPERTIES (property_name = property_value, ...)]
AS select_statement;

CREATE VIEW IF NOT EXISTS default.product_sales_view (

ALTER VIEW [db_name.]view_name AS select_statement;

ALTER VIEW default.product_sales_view AS
SELECT product_id,SUM(amount) FROM default.orders
WHERE order_date = '2024-09-03' GROUP BY product_id;

ALTER VIEW [db_name.]view_name
SET TBLPROPERTIES (property_name=property_value,...);

ALTER VIEW default.product_sales_view
SET TBLPROPERTIES (

DROP VIEW [IF EXISTS] [db_name.]view_name;

DROP VIEW IF EXISTS default.product_sales_view;

ALTER MATERIALIZED VIEW [db_name.]materialized_view_name ENABLE|DISABLE REWRITE;

SHOW MATERIALIZED VIEWS [IN db_name] [LIKE pattern];

SHOW MATERIALIZED VIEWS IN default;

ALTER MATERIALIZED VIEW [db_name.]materialized_view_name REBUILD;

DROP MATERIALIZED VIEW [db_name.]materialized_view_name;

DROP MATERIALIZED VIEW default.mv_daily_product_sales;

ALTER TABLE remote.PARTITION_KEYS
MODIFY COLUMN `PKEY_COMMENT` VARCHAR(4000)
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

SHOW PARTITIONS default.page_view;

ALTER TABLE [db_name.]table_name_tag
EXCHANGE PARTITION (col=value[,col=value,...])
WITH TABLE [db_name.]table_name_src

ALTER TABLE default.page_view_new
EXCHANGE PARTITION (dt = '20250901', country = 'beijing')
WITH TABLE default.page_view;
STEP 02

ALTER TABLE default.page_view DROP IF EXISTS PARTITION (dt='20250903');

TRUNCATE [db_name.]table_name PARTITION (col=value[,col=value,...]);

LOAD DATA INPATH '/hive/data/chapter4/orders_2025_08.csv'
OVERWRITE INTO TABLE chapter4.orders
PARTITION (year='2025',month='08');

INSERT INTO TABLE [db_name.]table_name
[PARTITION (col[=partvalue][,col[=partvalue],...])]
VALUES (value[,value,...])[,(value[,value,...]),...]

INSERT OVERWRITE TABLE [db_name.]table_name
[PARTITION (col=value[,col=value,...])]
[IF NOT EXISTS]] select_statement;
INSERT INTO TABLE [db_name.]table_name
[PARTITION (col=value[,col=value,...])]
select_statement;

ALTER TABLE [db_name.]table_name
[PARTITION (col[=partvalue][,col[=partvalue],...])] COMPACT 'minor';

ALTER TABLE [db_name.]table_name
[PARTITION (col[=partvalue][,col[=partvalue],...])] COMPACT 'major';

EXPORT TABLE [db_name.]table_name
[PARTITION (col=value[,col=value,...])]
TO export_target_path;

IMPORT [[EXTERNAL] TABLE [db_name.]table_name
[PARTITION (col=value[,col=value,...])]]
FROM source_path
[LOCATION hdfs_path]

SELECT loc,temp,avg(temp) OVER (PARTITION BY loc) AS temp_avg
FROM chapter6.demo_iot;

dense_rank() OVER (PARTITION BY shop_id ORDER BY revenue DESC) AS d_rank
FROM chapter6.demo_sales_daily
WHERE shop_id = 101;

STEP 01
INSERT INTO itcast_dws.consult_dws PARTITION (yearinfo,monthinfo)

[WITH CommonTableExpression (,CommonTableExpression)*]
SELECT [ALL | DISTINCT] select_expr,select_expr,...
FROM table_reference
[WHERE where_condition]
[GROUP BY group_col_list]
[ORDER BY order_col_list]
[CLUSTER BY cluster_col_list

SELECT select_expr,select_expr,... FROM
[db_name.]table_name TABLESAMPLE(BUCKET x OUT OF y [ON bucket_col]);

SELECT order_id,year,month
FROM chapter5.sales_fact TABLESAMPLE(BUCKET 2 OUT OF 3 ON category);

CREATE TABLE orders (
order_id INT,
customer STRUCT<
id: INT,
name: STRING,
address: STRUCT<city: STRING, zip: STRING>
>,
items ARRAY<STRUCT<product_id: INT, name: STRING, price: DOUBLE>>,
metadata MAP<STRING, STRING>
);

CREATE TABLE IF NOT EXISTS users (
user_id INT,
username STRING,
email STRING,
gender STRING,
age INT,
registration_date STRING
);
STEP 12

ALTER TABLE remote.DBS
MODIFY COLUMN `DESC` VARCHAR(4000)
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

ALTER TABLE remote.DATABASE_PARAMS
MODIFY COLUMN `PARAM_KEY` VARCHAR(180) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
MODIFY COLUMN `PARAM_VALUE` VARCHAR(4000) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE remote.COLUMNS_V2
MODIFY COLUMN `COMMENT` VARCHAR(4000)
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

ALTER TABLE remote.TABLE_PARAMS
MODIFY COLUMN `PARAM_KEY` VARCHAR(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
MODIFY COLUMN `PARAM_VALUE` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

SHOW TABLES [IN db_name] [pattern];

SHOW TABLES IN default;

DESC FORMATTED default.order_detail;

SHOW CREATE TABLE [db_name.]table_name;

ALTER TABLE [db_name.]table_name RENAME TO [db_name.]new_table_name;

ALTER TABLE default.order_detail RENAME TO default.dwd_order_detail;

ALTER TABLE [db_name.]table_name
SET TBLPROPERTIES (property_name=property_value,...);

ALTER TABLE default.dwd_order_detail SET TBLPROPERTIES ('immutable'='TRUE');

ALTER TABLE [db_name.]table_name CHANGE col_old_name col_new_name data_type [COMMENT col_comment] [FIRST|AFTER column_name];

ALTER TABLE default.dwd_order_detail CHANGE order_id orderid STRING AFTER user_id;

ALTER TABLE [db_name.]table_name ADD COLUMNS (col_name data_type [COMMENT col_comment],...)

ALTER TABLE default.dwd_order_detail
ADD COLUMNS (

DROP TABLE [IF EXISTS] [db_name.]table_name [PURGE];

DROP TABLE IF EXISTS default.dwd_order_detail;

CREATE TABLE IF NOT EXISTS default.page_view(
viewTime INT,
userid BIGINT,
page_url STRING,
referrer_url STRING,
ip STRING

CREATE TABLE IF NOT EXISTS default.orders (
order_id INT,
product_id INT,
user_id INT,
amount DOUBLE,
order_date STRING

CREATE TEMPORARY TABLE default.tmp_sales (
id BIGINT,
product STRING,
amount DOUBLE

SELECT order_id,(price * quantity - discount ) AS order_amount
FROM chapter5.sales_fact
ORDER BY order_amount DESC
LIMIT 3;
STEP 01

CREATE TABLE IF NOT EXISTS chapter5.sales_fact_new
LIKE chapter5.sales_fact;
STEP 02

SELECT sid_total,province
FROM itcast_dws.visit_dws
WHERE yearinfo='2025' AND monthinfo='07' AND grouptype=1
ORDER BY sid_total DESC LIMIT 6;

SELECT sid_total,from_url
FROM itcast_dws.visit_dws
WHERE yearinfo='2025' AND monthinfo='07'
AND grouptype=3 AND from_url IS NOT NULL
ORDER BY sid_total DESC LIMIT 3;

LOAD DATA LOCAL INPATH '/export/data/chapter4/orders_np.json'
OVERWRITE INTO TABLE chapter4.orders_np;

LOAD DATA INPATH '/hive/data/chapter4/orders_2025_09.csv'
INTO TABLE chapter4.orders;

LOAD DATA INPATH '/hive/data/chapter5/sales_fact.csv'
OVERWRITE INTO TABLE chapter5.sales_fact;

INSERT INTO TABLE users VALUES
(1,'zhangsan','zhangsan@example.com','male',25,'2025-01-15'),
(2,'lisi','lisi@example.com','female',30,'2025-02-20'),
(3,'wangwu','wangwu@example.com','male',28,'2025-03-05'),
(4,'zhaoliu','zhaoliu@example.com','female',22,'2025-04-18'),
(5,'qianqi','qianqi@example.com','male',35,'2025-05-22'),
(6,'sunba','sunba@example.com','female',27,'2025-06-30');
STEP 12

INSERT OVERWRITE TABLE …
[INSERT INTO TABLE …]
…;

FROM chapter4.orders
INSERT INTO TABLE chapter4.orders_user_quantities
SELECT user_id,quantities,year,month
INSERT INTO TABLE chapter4.orders_user_client
SELECT user_id,user_client,year,month

INSERT OVERWRITE LOCAL DIRECTORY '/export/data/chapter4/export'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
COLLECTION ITEMS TERMINATED BY '|'
MAP KEYS TERMINATED BY ':'
SELECT * FROM chapter4.orders;

IMPORT TABLE chapter4.orders_copy FROM '/hive/data/chapter4/orders_2025_08';

DELETE FROM [db_name.]table_name [WHERE expression];

ADD JAR path;
CREATE TEMPORARY FUNCTION function_name AS class_name;

CREATE FUNCTION [db_name.]function_name AS class_name
[USING JAR path [,JAR path]];

ADD JAR /export/data/chapter6/HiveFunction.jar;
CREATE TEMPORARY FUNCTION sum_avg_format AS 'cn.itcast.udaf.SumAvgFormat';

CREATE USER 'itcast'@'%' IDENTIFIED BY 'Itcast@2025';
GRANT ALL PRIVILEGES ON *.* TO 'itcast'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

WITH DBPROPERTIES (

SELECT current_database();

ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (

AS SELECT product_id,SUM(amount)
FROM default.orders GROUP BY product_id;

AS SELECT order_date,product_id,SUM(amount) AS revenue
FROM orders GROUP BY order_date, product_id;

SELECT * FROM remote.SCHEDULED_QUERIES \G

UPDATE [db_name.]table_name SET column=value [,column=value,...]
[WHERE expression];

UPDATE chapter4.orders_transaction
SET items=array('SKU008'),quantities=map('SKU008',2)
WHERE year='2025' AND month='09' AND order_id=10007;

SELECT t.order_id,t.commodity FROM (
SELECT order_id,items[0] AS commodity FROM chapter4.orders) t;

SELECT order_id,user_id,item_id,province
FROM chapter5.sales_fact WHERE province = 'Beijing';

SELECT order_id,user_id,item_id,device
FROM chapter5.sales_fact WHERE device IS NULL;

SELECT order_id,user_id,item_id,order_time
FROM chapter5.sales_fact WHERE order_time
BETWEEN timestamp '2025-08-20 00:00:00' AND timestamp '2025-08-21 23:59:59';

SELECT order_id, order_amount
FROM (

SELECT order_id,order_time FROM chapter5.sales_fact
WHERE year = '2025' AND month = '09';
STEP 01

SELECT order_id,category,order_time FROM chapter5.sales_fact
WHERE year='2025' AND month='09' AND category IN ('Sports','Grocery');
STEP 02

WITH cte_name AS (select_statement)
[,cte_name AS (select_statement),…]

WITH t1 AS (
SELECT order_id,(price * quantity - discount) AS order_amount,order_time
FROM chapter5.sales_fact WHERE year='2025' AND month IN ('08','09')

SELECT order_id,order_amount,order_time FROM t1 WHERE order_amount > 1000;

SELECT province,SUM(price * quantity - discount) AS total_amount
FROM chapter5.sales_fact
GROUP BY province;
STEP 02

WITH t1 AS (
SELECT province,(price * quantity - discount ) AS order_amount
FROM chapter5.sales_fact

SELECT province, SUM(order_amount) AS total_amount
FROM t1 GROUP BY province HAVING SUM(order_amount) >1000;

SELECT province, SUM(price * quantity - discount) AS total_amount
FROM chapter5.sales_fact
GROUP BY province
ORDER BY total_amount DESC;
STEP 02

SELECT province, (price * quantity - discount) AS total_amount,order_id
FROM chapter5.sales_fact
ORDER BY province,total_amount DESC;
STEP 03

SELECT order_id,(price * quantity - discount ) AS order_amount
FROM chapter5.sales_fact
SORT BY order_amount DESC;
STEP 02

SELECT order_id,(price * quantity - discount ) AS order_amount,province
FROM chapter5.sales_fact
DISTRIBUTE BY province SORT BY order_amount;
STEP 03

SELECT order_id,(price * quantity - discount ) AS order_amount
FROM chapter5.sales_fact
UNION ALL
SELECT order_id,(price * quantity - discount ) AS order_amount
FROM chapter5.sales_fact_new;
STEP 03

SELECT select_expr,select_expr,... FROM
[db_name.]table_name1
[INNER] JOIN [db_name.]table_name2 ON expression
[[INNER] JOIN [db_name.]table_name3 ON expression

SELECT select_expr,select_expr,... FROM
[db_name.]table_name1

SELECT select_expr,select_expr,... FROM
[db_name.]table_name1
LEFT SEMI JOIN [db_name.]table_name2 ON expression
[LEFT SEMI JOIN [db_name.]table_name3 ON expression

SELECT select_expr,select_expr,... FROM
[db_name.]table_name1
CROSS JOIN [db_name.]table_name2
[CROSS JOIN [db_name.]table_name3

SELECT a.val, b.val
FROM a LEFT JOIN b
ON a.key=b.key AND b.ds='2025-07-07' AND a.ds='2025-07-07';

SELECT select_expr,select_expr,... FROM
[db_name.]table_name TABLESAMPLE(size);

SELECT order_id,year,month FROM chapter5.sales_fact TABLESAMPLE(100B);

SELECT select_expr,select_expr,... FROM
[db_name.]table_name TABLESAMPLE(p PERCENT);

SELECT order_id,year,month
FROM chapter5.sales_fact TABLESAMPLE(80 PERCENT);

SELECT select_expr,select_expr,... FROM
[db_name.]table_name TABLESAMPLE(n ROWS);

SELECT order_id,year,month
FROM chapter5.sales_fact TABLESAMPLE(2 ROWS);
STEP 02

SELECT * FROM chapter5.sales_fact
DISTRIBUTE BY RAND() SORT BY RAND() LIMIT 5;

SELECT loc,
count(*) AS total_count,
count(temp) AS temp_total_count,
avg(humidity) AS humidity_avg,
min(temp) AS temp_min
FROM chapter6.demo_iot GROUP BY loc;

SET hive.exec.reducers.bytes.per.reducer=64;

set hive.fetch.task.conversion=none;

SET hive.exec.parallel=true;

SET hive.exec.mode.local.auto=true;

SET hive.groupby.skewindata=true;

SET hive.exec.compress.intermediate=true;
SET tez.runtime.compress=true;
SET tez.runtime.compress.codec=org.apache.hadoop.io.compress.SnappyCodec;