Hive中常见Sql有哪些

这篇文章主要为大家展示了“Hive中常见Sql有哪些”，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下“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有哪些”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注亿速云行业资讯频道！