Hive Data Definition Language
来源:互联网 发布:sql怎么设置默认值 编辑:程序博客网 时间:2024/06/18 13:50
HiveQL DDL statements are documented here, including: CREATE DATABASE/SCHEMA, TABLE, VIEW, FUNCTION, INDEX DROP DATABASE/SCHEMA, TABLE, VIEW, INDEX TRUNCATE TABLE ALTER DATABASE/SCHEMA, TABLE, VIEW MSCK REPAIR TABLE (or ALTER TABLE RECOVER PARTITIONS) SHOW DATABASES/SCHEMAS, TABLES, TBLPROPERTIES, PARTITIONS, FUNCTIONS, INDEX[ES], COLUMNS, CREATE TABLE DESCRIBE DATABASE/SCHEMA, table_name, view_namePARTITION statements are usually options of TABLE statements, except for SHOW PARTITIONS.1.Create/Drop/Alter/Use DatabaseCREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name [COMMENT database_comment] [LOCATION hdfs_path] [WITH DBPROPERTIES (property_name=property_value, ...)]; 2.Drop DatabaseDROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];3.Alter DatabaseALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, ...); -- (Note: SCHEMA added in Hive 0.14.0) ALTER (DATABASE|SCHEMA) database_name SET OWNER [USER|ROLE] user_or_role; -- (Note: Hive 0.13.0 and later; SCHEMA added in Hive 0.14.0)4.Use DatabaseUSE database_name;USE DEFAULT;To check which database is currently being used: SELECT current_database() (as of Hive 0.13.0).5.Create TableCREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name -- (Note: TEMPORARY available in Hive 0.14.0 and later) [(col_name data_type [COMMENT col_comment], ...)] [COMMENT table_comment] [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] [CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] [SKEWED BY (col_name, col_name, ...) -- (Note: Available in Hive 0.10.0 and later)] ON ((col_value, col_value, ...), (col_value, col_value, ...), ...) [STORED AS DIRECTORIES] [ [ROW FORMAT row_format] [STORED AS file_format] | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)] -- (Note: Available in Hive 0.6.0 and later) ] [LOCATION hdfs_path] [TBLPROPERTIES (property_name=property_value, ...)] -- (Note: Available in Hive 0.6.0 and later) [AS select_statement]; -- (Note: Available in Hive 0.5.0 and later; not supported for external tables) CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name LIKE existing_table_or_view_name [LOCATION hdfs_path]; data_type : primitive_type | array_type | map_type | struct_type | union_type -- (Note: Available in Hive 0.7.0 and later) primitive_type : TINYINT | SMALLINT | INT | BIGINT | BOOLEAN | FLOAT | DOUBLE | STRING | BINARY -- (Note: Available in Hive 0.8.0 and later) | TIMESTAMP -- (Note: Available in Hive 0.8.0 and later) | DECIMAL -- (Note: Available in Hive 0.11.0 and later) | DECIMAL(precision, scale) -- (Note: Available in Hive 0.13.0 and later) | DATE -- (Note: Available in Hive 0.12.0 and later) | VARCHAR -- (Note: Available in Hive 0.12.0 and later) | CHAR -- (Note: Available in Hive 0.13.0 and later) array_type : ARRAY < data_type > map_type : MAP < primitive_type, data_type > struct_type : STRUCT < col_name : data_type [COMMENT col_comment], ...> union_type : UNIONTYPE < data_type, data_type, ... > -- (Note: Available in Hive 0.7.0 and later) row_format : DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char] [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char] [NULL DEFINED AS char] -- (Note: Available in Hive 0.13 and later) | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)] file_format: : SEQUENCEFILE | TEXTFILE -- (Default, depending on hive.default.fileformat configuration) | RCFILE -- (Note: Available in Hive 0.6.0 and later) | ORC -- (Note: Available in Hive 0.11.0 and later) | PARQUET -- (Note: Available in Hive 0.13.0 and later) | AVRO -- (Note: Available in Hive 0.14.0 and later) | INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname6.Partitioned Tablescreate table table_name ( id int, dtDontQuery string, name string)partitioned by (date string)CREATE TABLE page_view(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(dt STRING, country STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'STORED AS SEQUENCEFILE;7.External TablesCREATE EXTERNAL TABLE page_view(viewTime INT, userid BIGINT, page_url STRING, referrer_url STRING, ip STRING COMMENT 'IP Address of the User', country STRING COMMENT 'country of origination') COMMENT 'This is the staging page view table' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\054' STORED AS TEXTFILE LOCATION '<hdfs_location>'; 8.Create Table As Select (CTAS) CREATE TABLE new_key_value_store ROW FORMAT SERDE "org.apache.hadoop.hive.serde2.columnar.ColumnarSerDe" STORED AS RCFile ASSELECT (key % 1024) new_key, concat(key, value) key_value_pairFROM key_value_storeSORT BY new_key, key_value_pair;9.Create Table LikeCREATE TABLE empty_key_value_storeLIKE key_value_store;10.Bucketed Sorted TablesCREATE TABLE page_view(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(dt STRING, country STRING) CLUSTERED BY(userid) SORTED BY(viewTime) INTO 32 BUCKETS ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001' COLLECTION ITEMS TERMINATED BY '\002' MAP KEYS TERMINATED BY '\003' STORED AS SEQUENCEFILE;
More info please refer to page : https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL
0 0
- Hive Data Definition Language
- Hive Data Definition Language
- Hive 5、Hive 的数据类型 和 DDL Data Definition Language) Hive DML(Data Manipulation Language)
- Hive DDL(Data Definition Language)之创建表
- Hive DDL(Data Definition Language)之修改表
- Hive Data Manipulation Language
- Hive 读书笔记3:HiveQL Data Definition(上)
- Hive 读书笔记4:HiveQL Data Definition(下)
- SQL教程:数据定义 DDL(Data Definition Language)
- DDL(Data Definition Language)语句 on Oracle
- MySql(2)------SQL基础之DDL(Data Definition Language)
- 深入学习《Programing Hive》:数据操纵DML(Data Manipulation Language)
- SQL Server 2005 - 数据定义语言触发器(Data Definition Language Triggers)[翻译] [转]
- 如何用Visio來做正向工程產生 DDL (Data Definition Language)
- mysql数据库导入和导出,删除和创建(ddl)(data definition language)
- Oracle数据库 —— SQL之ddl(Data definition language)语句
- 05--MySQL自学教程:DDL(Data Definition Language:数据库定义语言)操作数据库(一)
- C programming language BNF definition
- Sum It Up
- X_Forward_For(XXF)获取用户IP
- 21、表单操作
- org.hibernate.exception.GenericJDBCException: Cannot release connection
- linux上传和下载文件(rz,sz)
- Hive Data Definition Language
- Android Bander设计与实现 - 设计篇
- Yii中配置单点登录 即多个子站同步登录。
- Android xml资源文件中@、@android:type、@*、?、@+含义和区别
- 自定义模态框
- Cocos2d-x 3.x 配置安卓环境以及移植安卓
- 完全PHP5笔记之(COOKIE与SESSION篇)
- Java FTP上传文件工具类
- 无法启动此程序,因为计算机中丢失VCRUNTIME140.dll 尝试重新安装此程序以解决此问题