Hive中常见Sql有哪些

这篇文章主要为大家展示了“Hive 中常见 Sql 有哪些”，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让丸趣 TV 小编带领大家一起研究并学习一下“Hive 中常见 Sql 有哪些”这篇文章吧。

•数据库
   show databases;
   CREATE DATABASE IF NOT EXISTS test;
   drop database test;
   use test;
•建表
     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, col_name, …) 
       [SORTED BY (col_name [ASC|DESC], …)] INTO num_buckets BUCKETS] 
       [ROW FORMAT row_format] 
       [STORED AS file_format] 
       [LOCATION hdfs_path]
     •CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXIST 选项来忽略这个异常
     •EXTERNAL 关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION）
     •LIKE 允许用户复制现有的表结构，但是不复制数据
     •COMMENT 可以为表与字段增加描述
     
     •ROW FORMAT
       DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char]
         [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
        | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, …)]
          用户在建表的时候可以自定义 SerDe 或者使用自带的 SerDe。如果没有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED，将会使用自带的 SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的 SerDe，Hive 通过 SerDe 确定表的具体的列的数据。
     •STORED AS
           SEQUENCEFILE
           | TEXTFILE
           | RCFILE    
           | INPUTFORMAT input_format_classname OUTPUTFORMAT             output_format_classname
          如果文件数据是纯文本，可以使用 STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCE。
     •hive 支持的字段类型
       TINYINT  
       SMALLINT  
       INT  
       BIGINT  
       BOOLEAN  
       FLOAT  
       DOUBLE  
       STRING
       
     •创建简单表
       CREATE TABLE IF NOT EXISTS pokes (foo STRING, bar STRING) 
       ROW FORMAT DELIMITED FIELDS TERMINATED BY ,  
       STORED AS TEXTFILE;
     •创建外部表
       CREATE EXTERNAL TABLE pokes (foo STRING, bar STRING) 
       ROW FORMAT DELIMITED FIELDS TERMINATED BY ,  
       STORED AS TEXTFILE
       LOCATION /test/pokes
     •建分区表
       CREATE TABLE IF NOT EXISTS invites (foo STRING, bar STRING) 
       PARTITIONED BY(d STRING,s STRING)
       ROW FORMAT DELIMITED FIELDS TERMINATED BY ,
       STORED AS TEXTFILE;
     •建 Bucket 表
       CREATE TABLE IF NOT EXISTS buckets (foo STRING, bar STRING) 
       CLUSTERED BY (foo) into 4 buckets 
       ROW FORMAT DELIMITED FIELDS TERMINATED BY ,
       STORED AS TEXTFILE;
     •复制一个空表
       CREATE TABLE invites_copy LIKE invites;
     •创建表并从其他表导入数据(mapreduce)
       CREATE TABLE parts AS SELECT * FROM invites;
     •hbase 表
       CREATE EXTERNAL TABLE workStatisticsNone (
       id string,
       num int
       ) STORED BY org.apache.hadoop.hive.hbase.HBaseStorageHandler
       WITH SERDEPROPERTIES (hbase.columns.mapping = :key,f:c)
       TBLPROPERTIES (hbase.table.name = workStatisticsNone , hbase.mapred.output.outputtable = workStatisticsNone

     
•删除表
   drop table pokes;
   drop table invites;
•修改表结构
   •增加 / 替换 / 修改列
     ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type[COMMENT col_comment], …)
     ALTER TABLE pokes ADD COLUMNS (d STRING COMMENT d comment
     ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENTcol_comment] [FIRST|(AFTER column_name)]  
     alter table pokes change d s string comment change column name first;
     
   •更改表名：
     ALTER TABLE pokes RENAME TO poke;
   •修复表分区：
     MSCK REPAIR TABLE invites;
     ALTER TABLE invites RECOVER PARTITIONS;
•创建/删除视图
   CREATE VIEW [IF NOT EXISTS] view_name [(column_name [COMMENT column_comment], …) ][COMMENT view_comment][TBLPROPERTIES (property_name = property_value, …)] AS SELECT
   create view v_invites(foo,bar) as select foo,bar from invites;
   DROP VIEW v_invites;
•显示命令
   SHOW TABLES;
   SHOW TABLES .*s（正则表达式）
   desc pokes;
   SHOW FUNCTIONS;
   DESCRIBE FUNCTION function_name
   DESCRIBE FUNCTION EXTENDED function_name
•加载数据
   •Load data 到指定的表
     LOAD DATA LOCAL INPATH kv.txt OVERWRITE INTO TABLE pokes;
     LOAD DATA LOCAL INPATH kv1.txt  INTO TABLE pokes;
     LOAD DATA INPATH /test/kv.txt INTO TABLE pokes;
     LOAD DATA INPATH /test/kv.txt INTO TABLE pokes;
      关键字[OVERWRITE] 意思是是覆盖原表里的数据，不写则不会覆盖。
      关键字 [LOCAL] 是指你加载文件的来源为本地文件，不写则为 hdfs 的文件。
   •load 到指定表的分区
     LOAD DATA LOCAL INPATH kv.txt OVERWRITE INTO TABLE invites PARTITION(d= 1 ,s= 1
     LOAD DATA LOCAL INPATH kv1.txt  INTO TABLE invites PARTITION(d= 1 ,s= 1
     LOAD DATA LOCAL INPATH kv.txt OVERWRITE INTO TABLE invites PARTITION(d= 1 ,s= 2
   •查询结果导入 hive
     INSERT overwrite TABLE pokes SELECT foo,bar FROM invites; 覆盖相应目录下的文件
     INSERT INTO TABLE pokes SELECT foo,bar FROM invites;
     INSERT INTO TABLE invites_copy PARTITION(d= 1 ,s= 1) SELECT * FROM invites;
      动态分区插入，默认关闭
     set hive.exec.dynamic.partition.mode=nonstrict
     INSERT INTO TABLE invites_copy PARTITION(d,s) SELECT * FROM invites;
   •多插入模式
     FROM from_statement
     INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 …)] select_statement1
     [INSERT OVERWRITE TABLE tablename2 [PARTITION …] select_statement2] …
   •查询结果写入文件系统
     INSERT OVERWRITE [LOCAL] DIRECTORY directory1 select_statement1
     insert overwrite local DIRECTORY   test.txt select * from invites_copy
•数据查询
   SELECT [ALL | DISTINCT] select_expr, select_expr, …
   FROM table_reference
   [WHERE where_condition]
   [GROUP BY col_list [HAVING condition]]
   [  CLUSTER BY col_list
     | [DISTRIBUTE BY col_list] [SORT BY| ORDER BY col_list]
   ]
   [LIMIT number]
   
   select * from invites limit 2,5;
   ORDER BY 与 SORT BY 的不同
     •ORDER BY 全局排序，只有一个 Reduce 任务
     •SORT BY 只在本机做排序
   hive 会根据 distribute by 后面列，根据 reduce 的个数进行数据分发，默认是采用 hash 算法
   cluster by 除了具有 distribute by 的功能外还兼具 sort by 的功能，但是排序只能是倒序排序
   select * from invites where foo=1 or bar=2;
     where 条件支持 AND,OR ,between,IN, NOT IN,EXIST,NOT EXIST
     
   •JOIN
   Hive 只支持等值连接（equality joins）、外连接（outer joins）和（left semi joins）。Hive 不支持所有非等值的连接，因为非等值连接非常难转化到 map/reduce 任务
     •join on 属于 common join
        最为普通的 join 策略，不受数据量的大小影响，也可以叫做 reduce side join
     •left semi joins
       left semi join 则属于 map join（broadcast join）的一种变体,left semi join 是只传递表的 join key 给 map 阶段 , 如果 key 足够小还是执行 map join, 如果不是则还是 common join, 代替 in 条件
       select a.* from invites a left semi join invites_copy b on (a.bar=b.bar)
     •Map Join
       SELECT /*+ MAPJOIN(smalltable)*/  .key,value
       FROM smalltable JOIN bigtable ON smalltable.key = bigtable.key
       0.7 之后，不需要 /*+ MAPJOIN(smalltable)*/，这个计算是自动化的，自动判断哪个是小表，哪个是大表
       set hive.auto.convert.join=true;   # 是否自动转换为 mapjoin
       set hive.mapjoin.smalltable.filesize=300000000; # 小表的最大文件大小，默认为 25000000，即 25M
       set hive.auto.convert.join.noconditionaltask=true; #是否将多个 mapjoin 合并为一个
       set hive.auto.convert.join.noconditionaltask.size=300000000; 
       # 多个 mapjoin 转换为 1 个时，所有小表的文件大小总和的最大值，例如，一个大表顺序关联 3 个小表 a(10M), b(8M),c(12M)
       FULL [OUTER] JOIN 不会使用 MapJoin 优化
     •Bucket Map Join
        当连接的两个表的 join key 就是 bucket column 的时候
       hive.optimize.bucketmapjoin= true

以上是“Hive 中常见 Sql 有哪些”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注丸趣 TV 行业资讯频道！