HQL语法
来源:互联网 发布:男性卫生知况 编辑:程序博客网 时间:2024/05/15 15:03
Hive SQL与标准SQL存在一些差异,但也是大同小异,HQL的基本语法为:
[ ] 中内容是可选的,{ }中内容是必选的,| 表示内容二选一,全大写单词为关键字
建表语法:
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
[(col_name data_type [COMMENT col_comment], ...)]
[COMMENT table_comment]
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
[CLUSTERED BY (col_name, ...) [STORED BY (col_name [ASC|DESC], ...) ] INTO num_buckets BUCKETS]
[
[ROW FORMAT row_format] [STORED AS file_format]
| STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)] (Note: only available starting with 0.6.0)
]
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)] (Note: only available starting with 0.5.0)
[AS select_statement] (Note: only available starting with 0.5.0)
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
LIKE existing_table_name
[LOCATION hdfs_path]
data_type:
: primitive_type
| array_type
| map_type
| struct_type
primitive_type:
: TINYINT
| SMALLINT
| INT
| BIGINT
| BOOLEAN
| FLOAT
| DOUBLE
| STRING
array_type
: ARRAY < data_type >
map_type
: MAP < primitive_type, data_type >
struct_type
: STRUCT < col_name : data_type [COMMENT col_comment], ... >
row_format
: DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char] [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
| SERED serde_name [WITH SERDEPROPERTIES (property_name=property_value, ...)]
file_format
: SEQUENCEFILE
| TEXTFILE
| RCFILE (Note: only available starting with 0.6.0)
| INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname
修改表语法:
(1) Add Partitions
ALTER TABLE table_name ADD [IF NOT EXISTS] partition_spec [LOCATION 'location'] partition_spec [LOCATION 'location2'] ...
partition_spec
: PARTITION (partition_col = partition_val, partition_col2 = partition_val2, ...)
(2) Drop Parititons
ALTER TABLE table_name DROP partition_spec, partition_spec, ...
(3) Rename Table
ALTER TABLE table_name RENAME TO new_table_name
(4) Change Column
ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name col_type [COMMENT col_comment] [FIRST|AFTER col_name]
(5) Add/Replace Columns
ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)
ADD是代表新增一字段,字段位置在所有列后面(partition 列前);REPLACE 则是表示替换表中所有字段。
建视图语法
CREATE VIEW [IF NOT EXISTS] view_name [(col_name [COMMENT col_comment], .. )]
[COMMENT view_comment]
[TBLPROPERTIES (property_name = property_value, ...)]
AS SELECT ...
查看表语法
(1) 查看当前库[指定库]中所有表名
SHOW TABLES [IN database] ;
(2) 查看表名,部分匹配
SHOW TABLES 'page.*' ;
SHOW TABLES '.*view' ;
(3) 查看某张表的所有Partition,如果没有就报错:
SHOW PARTITIONS table_name ;
(4) 查看某张表的结构:
DESC [EXTENDED | FORMATTED] table_name ;
(5) 查看分区内容
SELECT * FROM table_name WHERE partition_col = partition_value ;
加载数据语法:
LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE table_name [PARTITION (partition_col = partition_value, partition_col2 = parititon_value2 , ...)]
插入语法:
1、Inserting data into Hive Table from queries
(1) Standard syntax:
INSERT OVERWRITE TABLE table_name [PARTITION (partition_col = partition_val, partition_col2 = partition_val2)] select_statement FROM from_statement ;
(2) Hive extension (multiple inserts):
FROM from_statement
INSERT OVERWRITE TABLE table_name1 [PARTITION partition_col = partiiton_val, partition_col2 = partition_val2 ... ] select_statement
[INSERT OVERWRITE TABLE table_name2 [PARTITION partition_col ...] select_statement2] ...
(3) Hive extension (dynamic partition inserts) :
INSERT OVERWRITE TABLE table_name PARTITION (partiition_col[=partition_val], partition_col2[=partition_val2] select_statement FROM from_statement
2、Writing data into filesystem from queries
(1) Standard syntax:
INSERT OVERWRITE [LOCAL] DIRECTORY directory select_statement ;
(2) Hive extension (multiple inserts) ;
FROM from_statement
INSERT OVERWRITE [LOCAL] DIRECTORY directory1 select_statement1
[INSERT OVERWRITE [LOCAL] DIRECTORY directory2 select_statement2] ...
删除语法:
DROP TABLE [IF EXISTS] table_name ;
删除一个内部表会同时删除表的元数据和数据;删除一个外部表,只删除元数据而保留数据。
查询语法:
SELECT [ALL | DISTINCT] select_expr, select_expr2, ...
FROM table_name
[WHERE where_condition]
[GROUP BY col_list]
[
[CLUSTER BY col_list | DISTRIBUTE BY col_list] [SORT BY col_list]
]
[LIMIT number]
关联语法:
table_reference [INNER] JOIN table_reference [join_condition]
| table_reference {LEFT | RIGHT | FULL} [OUTER] JOIN table_reference join_condition
| table_reference LEFT SEMI JOIN table_reference join_condition
table_reference
: table_name
| table_subquery alias
| (table_references)
join_condition
: ON expr = expr [AND expr2=expr2]...
- HQL语法
- HQL语法
- hql 语法
- HQL语法
- HQL语法
- HQL语法
- HQL语法
- HQL语法
- HQL语法
- HQL语法
- HQL语法
- NHibernate HQL相关语法
- HQL查询及语法
- HQL语法结构
- HQL查询及语法
- HQL查询及语法
- hql语法明细
- HQL查询及语法
- POJ 1061 青蛙的约会
- AAA Quartz-Spring[一]之MethodInvokingJobDetailFactoryBean配置任务
- HDU1142-A Walk Through the Forest(记忆化搜索+SPFA)
- 计算机科学篇笔记(一)
- Sprin MVC中DispatcherServlet和ContextLoaderListener的关系
- HQL语法
- javax.el.ELResolver错误
- 第一行代码系列第二章——在活动中使用menu以及销毁一个活动
- Java多线程 -- JUC包源码分析13 -- Callable/FutureTask源码分析
- JVM结构、GC工作机制详解
- 给 nanopi m2 Android系统安装busybox
- 字符串匹配
- 2016 ACM/ICPC Asia Regional Dalian Online Sparse Graph(BFS)
- [LeetCode] 147. Insertion Sort List