Hive编程指南03---Hive数据表

Hive数据表

1.创建数据表

    CREATE TABLE IF NOT EXISTS <database>.<table>

        name STRING COMMENT 'name',

        salary FLOAT COMMENT 'salary',

        subordinates ARRAY<STRING> COMMENT 'Names of subordinates',

        deductions MAP<STRING, FLOAT> COMMENT 'Keys are deductions names, values are percentages',

        address STRUCT<street:STRING, city:STRING, state:STRING, zip:INT> COMMENT ''Home address)

    COMMENT 'Description of the table'

    TBLPROPERTIES ('creator'='me', 'created_at'='2012-01-02',...)

    LOCATION '/user/hive/warehosue/mydb.db/...';

2.查找表

    SHOW TABLES IN <database>;

    SHOW TABLES '前缀.*'; //正则过滤表名

    注：IN <database>和表名正则表达式不能同时使用

3.查看表的详细表结构

    DESCRIBE EXTENDED <database>.<table>; //FORMATTED比EXTENDED内容更详细

4.创建外部表

    CREATE EXTERNAL TABLE IF NOT EXISTS <table>//外部表关键字

    LOCATION '/目录';//Hive数据目录位置

    注：删除该表不会删除掉这份数据（HDFS目录下还有这批数据），不过描述表的元数据信息会被删除

5.拷贝表

    CREATE EXTERNAL TABLE IF NOT EXISTS <database>.<new_table>

    LIKE <database>.<old_table>

    无EXTERNAL关键字且源表为外部表，新表为外部表

    无EXTERNAL关键字且源表为内部表，新表为内部表

    有EXTERNAL关键字且源表为内部表，新表为外部表

6.分区表

    CREATE TABLE <table>(...)

    PARTITIONED BY (分区1 数据类型, 分区2 数据类型);

7.分区表全表查询

    set hive.mapred.mode=strict; //禁止提交WHERE没有加分区过滤的查询

    set hive.mapred.mode=nonstrict; //允许提交WHERE没有加分区过滤的查询

8.查询分区

    SHOW PARTITIONS <table> PARTITION(分区='分区值');

9.外部分区表

    CREATE EXTERNAL TABLE IF NOT EXISTS log_messages(

        hms INT,

        severity STRING,

        server STRING,

        process_id INT,

        message STRING)

    PARTITIONED BY (year INT, month INT, day INT)

    ROW FORMAT DELIMITED FIELDS TERMINATED BY '/t';

    //增加一个分区

    ALTER TABLE log_messages ADD PARTITION(year = 2012, month = 1,day =  2)

    LOCATION 'hdfs://master_server/data/log_message/2012/1/2';

    //在廉价机上转移旧数据

    //a.将分区下的数据拷贝到S3中，例如，用户可以使用hadoop distcp命令：

    hadoop distcp /data/log_message/2011/12/02 s3n://ourbucket/logs/2011/12/02

    //b.修改表，将分区路径指向到S3路径：

    ALTER TABLE log_messages PARTITION(year = 2011,month = 12 day = 2)

    SET LOCATION 's3n://ourbucket/logs/2011/12/02'

    //c.使用hadoop fs -rmr 命令删除掉HDFS中的这个分区数据：

    hadoop fs -rmr /data/log_messages/2011/01/02

10.自定义表的存储格式

    STORED AS TEXTFILE; //每一行被认为是一个单独的记录

11.删除表

    DROP TABLE IF EXISTS <table>;

12.修改表

    a.表重命名

        ALTER TABLE <old_table> RENAME TO <new_table>

    b.增加、修改、删除表分区（通常外部表）

        ALTER TABLE <table> ADD IF NOT EXISTS

        PARTITION (year = 2011, month = 1, day = 1) LOCATION '/logs/2011/01/01'

        PARTITION (year = 2011, month = 1, day = 2) LOCATION '/logs/2011/01/02'

        PARTITION (year = 2011, month = 1, day = 3) LOCATION '/logs/2011/01/03'

        //同时，可以通过移动位置来修改某个分区的路径

        ALTER TABLE <table> PARTITION(year = 2011, month = 12, day = 2)

        SET LOCATION 's3n://ourbucket/logs/2011/01/02';

        //不会将数据从旧的路径转走，也不会删除旧的数据，可以通过DROP PARTITION删除分区；外部表分区内的数据不会被删除

        ALTER TABLE <table> DROP IF EXISTS PARTITION(year = 2011, month = 12, day = 2);

    c.修改列信息

        ALTER TABLE <table>

        CHANGE COLUMN <old_column> <new_column> <new_column_dataType>

        COMMENT 'comment'

        AFTER/FIRST <other_column>; //移动字段位置，AFTER之后，FIRST之前

    d.增加列

        ALTER TABLE <table> ADD COLUMNS(

            new_column new_column_dataType COMMENT 'comment',

            new_column new_column_dataType COMMENT 'comment');

    e.删除或者替换列

        //移除之前所有的字段并重新指定了新的字段；移除了server和process_id，只有表的元数据信息改变了，不会修改数据

        ALTER TABLE <table> REPLACE COLUMNS(

            hours_mins_secs INT COMMENT 'comment',

            severity STRING COMMENT 'comment'

            message STRING COMMENT 'comment');

    f.修改表属性

        //可以增加附加的表属性或者修改已经存在的属性，但是无法删除属性：

        ALTER TABLE <table> SET TBLPROPERTIES(

            'notes' = '....');

    g.修改存储属性

        ALTER TABLE <table>

        PARTITION(year = 2012, month = 1, day  = 1)

        SET FILEFORMAT SEQUENCEFILE;

        //如果是分区表，需要使用PARTITION子句

        ALTER TABLE <table>

        SET SERDE 'com.example.JSONSerDe'

        WITH SERDEPROPERTIES(

            'prop1' = 'value1',

            'prop2' = 'value2');

    h.其他修改语句

        //执行钩子：表中存储的文件在Hive之外被修改，触发钩子的执行

        ATLER TABLE <table> TOUCH PARTITION();

        //若表或分区不存在，钩子执行不会创建表或者分区，需要其他策略

        //将这个分区内的文件打包成一个Hadoop压缩包(HAR)文件，仅仅降低了文件系统中的文件数以及减轻NameNode的压力，不会减少任何存储空间

        ALTER TABLE <table> ARCHIVE PARTITION();//反向操作UNARCHIVE，只适用于表的独立分区

        //防止分区被删除和被查询，DISABLE反向操作

        ALTER TABLE <table> PARTITION() ENABLE NO_DROP;

        ALTER TABLE <table> PARTITION() ENABLE OFFLINE;