data/scripts/q_test_init.sql

0001 --
0002 -- Table src
0003 --
0004 DROP TABLE IF EXISTS src;
0005
0006 CREATE TABLE src (key STRING, value STRING) STORED AS TEXTFILE;
0007
0008 LOAD DATA LOCAL INPATH "${hiveconf:test.data.dir}/kv1.txt" INTO TABLE src;
0009
0010 --
0011 -- Table src1
0012 --
0013 DROP TABLE IF EXISTS src1;
0014
0015 CREATE TABLE src1 (key STRING, value STRING) STORED AS TEXTFILE;
0016
0017 LOAD DATA LOCAL INPATH "${hiveconf:test.data.dir}/kv3.txt" INTO TABLE src1;
0018
0019 --
0020 -- Table src_json
0021 --
0022 DROP TABLE IF EXISTS src_json;
0023
0024 CREATE TABLE src_json (json STRING) STORED AS TEXTFILE;
0025
0026 LOAD DATA LOCAL INPATH "${hiveconf:test.data.dir}/json.txt" INTO TABLE src_json;
0027
0028
0029 --
0030 -- Table src_sequencefile
0031 --
0032 DROP TABLE IF EXISTS src_sequencefile;
0033
0034 CREATE TABLE src_sequencefile (key STRING, value STRING) STORED AS SEQUENCEFILE;
0035
0036 LOAD DATA LOCAL INPATH "${hiveconf:test.data.dir}/kv1.seq" INTO TABLE src_sequencefile;
0037
0038
0039 --
0040 -- Table src_thrift
0041 --
0042 DROP TABLE IF EXISTS src_thrift;
0043
0044 CREATE TABLE src_thrift
0045 ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.thrift.ThriftDeserializer'
0046 WITH SERDEPROPERTIES (
0047   'serialization.class' = 'org.apache.hadoop.hive.serde2.thrift.test.Complex',
0048   'serialization.format' = 'com.facebook.thrift.protocol.TBinaryProtocol')
0049 STORED AS SEQUENCEFILE;
0050
0051 LOAD DATA LOCAL INPATH "${hiveconf:test.data.dir}/complex.seq" INTO TABLE src_thrift;
0052
0053
0054 --
0055 -- Table srcbucket
0056 --
0057 DROP TABLE IF EXISTS srcbucket;
0058
0059 CREATE TABLE srcbucket (key INT, value STRING)
0060 CLUSTERED BY (key) INTO 2 BUCKETS
0061 STORED AS TEXTFILE;
0062
0063 LOAD DATA LOCAL INPATH "${hiveconf:test.data.dir}/srcbucket0.txt" INTO TABLE srcbucket;
0064 LOAD DATA LOCAL INPATH "${hiveconf:test.data.dir}/srcbucket1.txt" INTO TABLE srcbucket;
0065
0066
0067 --
0068 -- Table srcbucket2
0069 --
0070 DROP TABLE IF EXISTS srcbucket2;
0071
0072 CREATE TABLE srcbucket2 (key INT, value STRING)
0073 CLUSTERED BY (key) INTO 4 BUCKETS
0074 STORED AS TEXTFILE;
0075
0076 LOAD DATA LOCAL INPATH "${hiveconf:test.data.dir}/srcbucket20.txt" INTO TABLE srcbucket2;
0077 LOAD DATA LOCAL INPATH "${hiveconf:test.data.dir}/srcbucket21.txt" INTO TABLE srcbucket2;
0078
0079
0080 --
0081 -- Table srcpart
0082 --
0083 DROP TABLE IF EXISTS srcpart;
0084
0085 CREATE TABLE srcpart (key STRING, value STRING)
0086 PARTITIONED BY (ds STRING, hr STRING)
0087 STORED AS TEXTFILE;
0088
0089 LOAD DATA LOCAL INPATH "${hiveconf:test.data.dir}/kv1.txt"
0090 OVERWRITE INTO TABLE srcpart PARTITION (ds="2008-04-08", hr="11");
0091
0092 LOAD DATA LOCAL INPATH "${hiveconf:test.data.dir}/kv1.txt"
0093 OVERWRITE INTO TABLE srcpart PARTITION (ds="2008-04-08", hr="12");
0094
0095 LOAD DATA LOCAL INPATH "${hiveconf:test.data.dir}/kv1.txt"
0096 OVERWRITE INTO TABLE srcpart PARTITION (ds="2008-04-09", hr="11");
0097
0098 LOAD DATA LOCAL INPATH "${hiveconf:test.data.dir}/kv1.txt"
0099 OVERWRITE INTO TABLE srcpart PARTITION (ds="2008-04-09", hr="12");
0100
0101
0102 DROP TABLE IF EXISTS primitives;
0103 CREATE TABLE primitives (
0104   id INT,
0105   bool_col BOOLEAN,
0106   tinyint_col TINYINT,
0107   smallint_col SMALLINT,
0108   int_col INT,
0109   bigint_col BIGINT,
0110   float_col FLOAT,
0111   double_col DOUBLE,
0112   date_string_col STRING,
0113   string_col STRING,
0114   timestamp_col TIMESTAMP)
0115 PARTITIONED BY (year INT, month INT)
0116 ROW FORMAT DELIMITED
0117   FIELDS TERMINATED BY ','
0118   ESCAPED BY '\\'
0119 STORED AS TEXTFILE;
0120
0121 LOAD DATA LOCAL INPATH "${hiveconf:test.data.dir}/types/primitives/090101.txt"
0122 OVERWRITE INTO TABLE primitives PARTITION(year=2009, month=1);
0123
0124 LOAD DATA LOCAL INPATH "${hiveconf:test.data.dir}/types/primitives/090201.txt"
0125 OVERWRITE INTO TABLE primitives PARTITION(year=2009, month=2);
0126
0127 LOAD DATA LOCAL INPATH "${hiveconf:test.data.dir}/types/primitives/090301.txt"
0128 OVERWRITE INTO TABLE primitives PARTITION(year=2009, month=3);
0129
0130 LOAD DATA LOCAL INPATH "${hiveconf:test.data.dir}/types/primitives/090401.txt"
0131 OVERWRITE INTO TABLE primitives PARTITION(year=2009, month=4);
0132