A detailed description of Hadoop Hive SQL syntax

Hive is best used for batch jobs that are based on large amounts of immutable data.

1. Build a table

CREATE Atbel [IF not EXISTS] table_name

(Col_name data_type)

CREATE table creates one and throws an exception if a table of the same name already exists. Use if not exists to ignore this exception.

such as: Cretae TABLE bcustomer (

cst_id Integer,

Cst_name STRING

);

To create an external table:

CREATE EXTERNAL TABLE Page_view (viewtime INT, UserID BIGINT, Page_url string, referrer_url string, IP string COMME NT ' IP Address of the User ', Country STRING COMMENT ' country of Origination ') COMMENT ' This is the staging page view T Able ' ROW FORMAT delimited fields TERMINATED by ' \054 ' STORED as textfile location ' Build Partition TableCREATE TABLE par_table (viewtime INT, UserID BIGINT, Page_url string, referrer_url string, IP string COMMENT ' IP Ad    Dress of the User ') COMMENT ' the Page view table ' partitioned by (date STRING, pos string) ROW FORMAT delimited ' \ t ' Fields TERMINATED by ' \ n ' STORED as sequencefile;Build Bucket TableCREATE TABLE par_table (viewtime INT, UserID BIGINT, Page_url string, referrer_url string, IP string COMMENT ' IP Ad Dress of the User ') COMMENT ' the Page view table ' partitioned by (date STRING, pos string) CLUSTERED by (userid) SOR TED by (Viewtime) into BUCKETS ROW FORMAT delimited ' \ t ' fields TERMINATED by ' \ n ' STORED as sequencefile;

Create a table and create an indexed field DS

Hive> CREATE TABLE invites (foo INT, bar string) partitioned by (DS string);

Copy an empty table (allows you to copy an existing table structure without copying the data)CREATE TABLE Empty_key_value_storeLike Key_value_store;Show All tables: show TABLES;table adds a column/update column:ALTER TABLE bcustomer add/replace COLUMNS (new_col INT)Add a column and add a column field Comment: Alter TABLE bcustomer add COLUMNS (new_col INT COMMENT ' a COMMENT ') Change name: ALTER TABLE Bcustomer RENAME to New NAME; Delete tables: drop table IF EXISTS IKEA. Temp_if_report_check_cherxu; Delete Column: ALTER TABLE table_name DROP column_name; There is no delete and update function in Hive SQL, you can use overwrite instead of if you want to delete cst_id=1 insert INTO TABLE bcustomerselect * from Bcustomer WHERE cst_i The equivalent connection is not supported in D!=1 hive select * from A, a where a.key=b.key (not allowed) should be written as: SELECT * from a join B on A.key=b.key

A detailed description of Hadoop Hive SQL syntax