[自分用メモ] Hiveのテーブル作成

テーブル作成例

create table customer (
  `c_custkey` bigint COMMENT '', 
  `c_name` string COMMENT '', 
  `c_address` string COMMENT '', 
  `c_nationkey` bigint COMMENT '', 
  `c_phone` string COMMENT '', 
  `c_acctbal` double COMMENT '', 
  `c_mktsegment` string COMMENT '', 
  `c_comment` string COMMENT '')
STORED AS PARQUET
LOCATION 'gs://bucket-1/customer';

テーブルの作成 Avro バージョン

CREATE  TABLE `customer`(
  `c_custkey` bigint COMMENT '', 
  `c_name` string COMMENT '', 
  `c_address` string COMMENT '', 
  `c_nationkey` bigint COMMENT '', 
  `c_phone` string COMMENT '', 
  `c_acctbal` double COMMENT '', 
  `c_mktsegment` string COMMENT '', 
  `c_comment` string COMMENT '', 
  `null` binary COMMENT '')
  STORED AS AVRO
  LOCATION 'gs://bucket-2/customer'
  TBLPROPERTIES("avro.compression"="SNAPPY");

他のテーブルからのデータの挿入

insert overwrite table customer select c_custkey, c_name, c_address, c_nationkey, c_phone, c_acctbal, c_mktsegm
ent, c_comment from db1.customer;

テーブルの作成 PRC パーティションあり

CREATE TABLE `lineitems`(
  `l_orderkey` bigint, 
  `l_linenumber` bigint, 
  `l_quantity` double, 
  `l_extendedprice` double, 
  `l_discount` double, 
  `l_tax` double, 
  `l_returnflag` string, 
  `l_linestatus` string, 
  `l_shipdate` date, 
  `l_recieptdate` date, 
  `l_shipinstruct` string, 
  `l_shipmode` string, 
  `o_orderkey` int, 
  `o_custkey` int, 
  `o_orderdate` date, 
  `o_totalprice` double, 
  `o_orderstatus` string, 
  `o_orderpriority` string, 
  `o_shippriority` int, 
  `o_clerk` string, 
  `c_custkey` bigint, 
  `c_nationkey` bigint, 
  `c_acctbal` double, 
  `c_mktsegment` string)
PARTITIONED BY (year int, month int, day int)
STORED AS ORC
LOCATION 'gs://bucket-3/';