首页 > 其他分享 >在hive中造数据

在hive中造数据

时间:2022-10-25 09:24:14浏览次数:37  
标签:... 中造 INSERT INTO hive 分区表 VALUES value 数据

在hive中造数据

1 . 创建表单

  • 分区表

    CREATE TABLE <table_name> (<column> <data_type>, <column> <data_type>, ...)
    CLUSTERED BY (<bucket_key>) INTO <n> BUCKETS
    
  • 单值分区表

    CREATE TABLE <table_name> (<column> <data_type>, <column> <data_type>, ...)
    PARTITIONED BY (<partition_key> <data_type>)
    CLUSTERED BY (<bucket_key>) INTO <n> BUCKETS
    STORED AS ORC
    TBLPROPERTIES ("transactional"="true");
    
  • 范围分区表

    CREATE TABLE <table_name> (<column> <data_type>, <column> <data_type>, ...)
    PARTITIONED BY RANGE(<partition_key1> <data_type>, <partition_key2> <data_type>, ...) (
      PARTITION [<partition_name_1>] VALUE LESS THAN(<key1_bound_value1>, <key2_bound_value1>, ...),
      PARTITION [partition_name_2] VALUE LESS THAN(key1_bound_value2, key2_bound_value2, ...),
      ...
    )
    CLUSTERED BY (<bucket_key>) INTO <n> BUCKETS
    STORED AS ORC
    TBLPROPERTIES ("transactional"="true"); 
    SET transaction.type=inceptor;
    

2.建表实例

  • 创建非分区ORC表

    DROP TABLE IF EXISTS tg;
    CREATE TABLE tg (name STRING, gpa DOUBLE) CLUSTERED BY (name) INTO 4 BUCKETS STORED AS ORC
    TBLPROPERTIES ("transactional"="true");
    
  • 创建单值分区ORC表

    DROP TABLE IF EXISTS test;
    CREATE TABLE test (a INT, b STRING, c DOUBLE) PARTITIONED BY (date STRING) CLUSTERED BY (c) INTO 8
    BUCKETS STORED AS ORC TBLPROPERTIES ("transactional"="true");
    
  • 创建范围分区ORC表

    DROP TABLE IF EXISTS t5;
    CREATE TABLE t5(id INT, value INT) PARTITIONED BY RANGE(amount INT)
    (
    PARTITION less1 VALUES LESS THAN (1),
    PARTITION less10 VALUES LESS THAN (10),
    PARTITION less100 VALUES LESS THAN (100)
    )
    CLUSTERED BY (id) INTO 5 BUCKETS STORED AS ORC TBLPROPERTIES ("transactional"="true");
    

3. 重点来了

语法:

​ (增删改)语法:

  	• INSERT INTO … VALUES

​ • UPDATE

​ • DELETE

  	• MERGE INTO 
-- 非分区表
INSERT INTO table_name VALUES (value, value, ...)
-- 单值分区表
INSERT INTO table_name PARTITION (partition_key = value) VALUES (value, value, ...)
-- 范围分区表
INSERT INTO table_name VALUES (value, value, ...) ①
INSERT INTO table_name PARTITION partition_name VALUES (value, value, ...)② 

4. 最终实例

SET transaction.type=inceptor;
-- 向非分区表单条插入
INSERT INTO ta VALUES ('Zhang San', 18);
-- 向单值分区表单条插入
INSERT INTO test PARTITION (date = '20150612')
VALUES (1, 'a', 1.0);
-- 向范围分区表单条插入
INSERT INTO t5 VALUES(0,5,99);
INSERT INTO t5 PARTITION less10 VALUES(0,5,9);

标签:...,中造,INSERT,INTO,hive,分区表,VALUES,value,数据
From: https://www.cnblogs.com/atao-BigData/p/16823793.html

相关文章