Hadoop Hive sql 语法详解

CREATE TABLE par_table(viewTime INT, userid BIGINT,      page_url STRING, referrer_url STRING,      ip STRING COMMENT ‘IP Address of the User‘)  COMMENT ‘This is the page view table‘  PARTITIONED BY(date STRING, pos STRING) ROW FORMAT DELIMITED ‘\t’    FIELDS TERMINATED BY ‘\n‘ STORED AS SEQUENCEFILE;

建Bucket表

CREATE TABLE par_table(viewTime INT, userid BIGINT,      page_url STRING, referrer_url STRING,      ip STRING COMMENT ‘IP Address of the User‘)  COMMENT ‘This is the page view table‘  PARTITIONED BY(date STRING, pos STRING)  CLUSTERED BY(userid) SORTED BY(viewTime) INTO 32 BUCKETS  ROW FORMAT DELIMITED ‘\t’    FIELDS TERMINATED BY ‘\n‘ STORED AS SEQUENCEFILE;

 

 

创建表并创建索引字段ds

hive> CREATE TABLE invites (foo INT, bar STRING) PARTITIONED BY (ds STRING);

 

复制一个空表 (允许复制现有的表结构，但不复制数据)

CREATE TABLE empty_key_value_store LIKE key_value_store;   显示所有表： SHOW TABLES;   表添加一列/更新列： ALTER TABLE BCUSTOMER ADD/REPLACE COLUMNS (new_col INT)   添加一列并增加列字段注释： ALTER TABLE BCUSTOMER ADD COLUMNS (new_col INT COMMENT ‘a comment‘)   更改表名： ALTER TABLE BCUSTOMER  RENAME TO NEWNAME;   删除表： DROP TABLE IF EXISTS IKEA.TEMP_IF_REPORT_CHECK_CHERXU;   删除列： ALTER TABLE table_name DROP column_name;   Hive SQL中没有delete 和update功能，可以用OVERWRITE代替 假如你要删除CST_ID=1的人 INSERT INTO TABLE BCUSTOMER SELECT * FROM BCUSTOMER  WHERE CST_ID !=1     HIVE中不支持等值连接 select * from a,b where a.key=b.key (不可以) 应该写为： select * from a join b on a.key=b.key

Hadoop Hive sql 语法详解

标签：port   string   exist   update   time   应用   partition   创建   creat