大数据开发实战:Hive表DDL和DML

  1、Hive 表 DDL

    1.1、创建表

    Hive中创建表的完整语法如下:

    CREATE [EXTERNAL] TABLE [IF NOT EXISTS]  table_name

      [ (col_name data_type [COMMET 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], . . . )  INFO num_buckets BUCKETS]

      [ROW FORMAT row_format]

      [STORED AS file_format]

      [LOCATION hdfs_path]

    CREATE  TABLE:用于创建一个指定名称的表,如果相同名字的表已经存在,则抛出异常,可以用 IF NOT EXISTS选项来忽略这个异常。

    EXTERNAL:该关键字可以让用户创建一个外部表,在创建表的同时指定一个指向实际数据的的路径(LOCATION).

    COMMENT:可以为表和字段增加注释

    ROW FORMAT:用户在建表的时候可以自定义SerDe或者使用自带的SerDe。如果没有指定的ROW FORMAT或者ROW FORMAT DELIMITED,将会使用自带的SerDe;在创建表时,用户还需要为表指定列,同时也会指定

            自定义的SerDe。Hive通过SerDe确定表的具体的列的数据。Serde是 Serializer/Deserializer的简写。hive使用Serde进行行对象的序列与反序列化。

      一条简单的建表语句如下:

      hive> CREATE TABLE  gripe(foo INT, bar STRING);

    Like:允许用户复制现有的表结构,但是不复制数据。例如:

      hive>CREATE TABLE empty_key_value_store LIKE  key_value_store

    另外还可以通过CREATE TABLE AS  SELECT的方式来创建表,例如:

      hive>CREATE TABLE new_key_value_store

         ROW FORMAT  SERDE  "org.apache.Hadoop.hive.serde2.columnar.ColumnarSerDe" STORED AS RCFile

         AS

         SELECT (key % 1024)  new_key , concat(key, value)  key_value_pair

         FROM  key_value_store

         SORT BY  new_key, key_value_pair;

    

    1.2、修改表

      修改表名的语法如下:

      hive> ALTER TABLE old_table_name  RENAME  TO  new_table_name;

      修改列名的语法如下:

      hive>ALTER  TABLE  table_name CHANGE  [COLUMN]  old_col_name  new_col_name  column_type  [COMMENT  col_comment] [FIRST|AFTER  column_name]

      上述语法允许改变列名、数据类型、注释、列位置或者它们的任意组合。建表后如果需要增加一列,则使用如下语法:

      hive>ALTER TABLE  gripe ADD COLUMNS (new_col  INT  COMMENT 'new col  comment');

    1.3、删除表

      DROP TABLE 语句用于删除表的数据和元数据。对应外部表,只删除Metastore中的元数据,而外部数据保存不动,例如:

      drop table my_table;

      如果只想删除表数据,保留表结构,跟MYSQL类似,使用TRUNCATE语句。

      TRUNCATE  TABLE my_table;

    1.4、插入表

      1、向表中加载数据

      LOAD DATA  [LOCAL] INPATH  'filepath'  [OVERWRITE]  INFO  TABLE  tablename [PARTITION(partcol1 = val1,  partcol2 = val2, . . .)]

      Load操作只是单纯的复制/移动操作,将数据 文件移动到Hive表对应的位置,filepath可以是相对路径,例如project/data1,  也可以是绝对路径,例如:/user/hive/project/data1, 或是包含模式的完整URI,例如:

      hdfs://namenode:9000/user/hive/project/data1。

      相对路径实例如下:

      hive> LOAD DATA  LOCAL  INPATH  './examples/files/kv1.txt'  OVERWRITE  INTO  TABLE gripe;

      2、将查询结果插入Hive表

      将查询结果写入HDFS文件系统。

        a、基本模式:

        INSERT OVERWRITE TABLE  tablename1  [PARTITION  (partcol1 = val1, partcol2 = val2, . .  .)]

        select_statements FROM from_statement

        b、多插入模式:

        INSERT OVERWRITE TABLE  tablename1

        [PARTITION (partcol1 = val1, partcol2 = val2, . . . )]

        select_statement1

        [ INSERT OVERWRITE TABLE  tablename2 [PARTITION . . . ]  select_statement2] . . .

        c、自动分区模式:

        INSERT OVERWRITE TABLE tablename PARTITION (partcol1[=val1], partcol2[=val2] . . . )

        select_statement From from_statement

  2、Hive 表 DML

    1、基本的select操作

    Hive中的select操作的语法如下:

    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]

    ALL和DISTINCT:选项区分对重复记录的处理。默认是ALL,表示查询所有记录。DISTINCT表示去掉重复记录。

    WHERE条件:类似于传统的SQL的where条件,支持 AND 、OR、BETWEEN、IN、NOT IN等。

    ORDER BY与SORT BY的不同:ORDER BY指全局排序,只有一个Reduce任务,而SORT BY只在本机做排序。

    LIMIT:可以限制查询的记录条数,例如:SELECT *  FROM t1  LIMIT  5, 也可以实现Top k查询,比如下面的查询语句可以查询销售记录最多的5个销售代表:

        SET mapred.reduce.tasks = 1

        SELECT *  FROM test SORT BY amount DESC LIMIT 5

    REGEX Column Specification:select 语句可以使用正则表达式做列选择,下面的语句查询除了ds和hr之外的所有列

    SELECT '(ds|hr)?+.+'  FROM  test

    

    2、join表

    Hive中join表的语言如下:

      join_table:

        table_reference [INNER] JOIN  table_factor  [join_condition]

        |  table_reference {LEFT|RIGHT|FULL}  [OUTER]  JOIN  table_reference  join_condition

        | table_reference LEFT  SEMI JOIN table_reference join_condition

        |table_reference  CROSS JOIN  table_reference [join_condition]  (as of Hive 0.10)

      table_reference:

        table_factor

        | join_table

      table_factor:

        tbl_name [alias]

        | table_subquery alias

        | (table_references)

      join_confition:

        On expression

    对Hive中表Join操作的说明以及注意事项如下:

    1、Hive只支持等值连接、外连接和左半连接(left semi join),Hive 不支持所有的非等值连接,因为非等值连接很难转化到map/reduce任务(从2.2.0版本后开始支持非等值连接)。

    2、可以连接2个以上的表,例如:

      SELECT a.val,  b.val,  c.val  FROM a  JOIN  b on  (a.key = b.key1)  JOIN  c  on (c.key = b .key2)

    3、如果连接中多个表的join key是同一个,则连接会转化为单个Map/Reduce任务,例如:

      SELECT a.val,  b.val,  c.val  FROM a  JOIN  b on  (a.key = b.key1)  JOIN  c  on (c.key = b .key1)

    4、join时大表放在最后。这是因为每次Map/Reduce任务的逻辑是这样的:Reduce会缓存join序列中除最后一个表之外的所有表的记录,再通过最后一个表将将结果序列化文件系统,因此在实践中,应该把最大的那个表写在最后。

    5、如果想限制join的输出,应该在where子句中写过滤条件,或是在join子句中写,但是表分区的情况很容易混淆,比如下面的第一个SQL语句所示,如果d表中找不到对应c表的记录,d表的所有列都会列出NULL,包括ds列,

      也就是说,join会过滤d表中不能找到 c表join key 的所有记录。这样,LEFT OUTER 就使得查询结果与WHERE子句无关,解决办法是在join时指定分区(如下面的第二个SQL语句)。

      //第一个SQL语句

      SELECT c.val, d.val  FROM  c  LEFT  OUTER  JOIN d  ON (c.key = d.key) where c.ds = '2010-08-08'  AND d.ds ='2010-08-08'

      

      //第一个SQL语句

      SELECT c.val, d.val  FROM  c  LEFT  OUTER  JOIN d  ON (c.key = d.key AND c.ds = '2010-08-08'  AND d.ds ='2010-08-08')

    6、LEFT SEMI JOIN是IN/EXISTS子查询的一种更高效的实现。其限制是:JOIN子句中右边的表只能在On子句中设置过滤条件,在WHERE子句、SELECT子句、或其他地方过滤都不行。

      SELECT a.key,  a.value  FROM  a  WHERE  a.key  in (SELECT b.key  FROM  b);

      可以被重写为:SELECT a.key,  a.value  FROM a  LEFT SEMI  JOIN  b on (a.key = b.key)

     

    参考资料:《离线和实时大数据开发实战》

上一篇:用JS获取地址栏中的参数的简易方法


下一篇:Eclipse html 编辑器插件下载安装