testbook

创建/清空/删除表：CREATE/TRUNCATE/DROP TABLE

所有可以在建表时用到的语法：
语法：

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
  [(col_name data_type [COMMENT 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], ...)] INTO num_buckets BUCKETS]
  [SKEWED BY (col_name, col_name, ...) ON ([(col_value, col_value, ...), ...|col_value, col_value, ...])
     [STORED AS DIRECTORIES]]
  [
   [ROW FORMAT row_format]
   [STORED AS file_format]
     | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]
  ]
  [LOCATION hdfs_path]
  [TBLPROPERTIES (property_name=property_value, ...)]
  [AS select_statement];

建表语法很多。在下面几节中，我们将分条详细解释上面语法中所有可能用到的子句，这些子句可以在同一条建表语句中出现，它们出现的顺序须遵循上面的语法。

简单的CREATE TABLE语句

CREATE TABLE创建一个给定名称的表。建表有三种方式：直接定义列，CREATE TABLE...LIKE和CREATE TABLE...AS SELECT (CTAS)

通过直接定义列创建表:

语法

CREATE TABLE table_name (col1 col_type1, col2 col_type2, ...)

示例
我们来为股票交易平台数据库our_platform创建一个用户信息表：

[$host]transwarp> CREATE TABLE user_info (
                    name             STRING,    //用户名字
                    acc_num          STRING,    //账户号码
                    password         STRING,    //账户密码
                    citizen_id       STRING,    //身份证号
                    bank_acc         STRING,    //绑定银行账户号码
                    reg_date         STRING,      //开户日期
                    acc_level        STRING     //账户级别
                  );

创建表时要声明列名和列中数据的数据类型。列中数据类型可以是原生或者复杂类别。这里我们创建了一个名为user_info的表，表中有七列：name, acc_num,password, citizen_id, bank_acc, reg_date和acc_level。

在指定数据库中创建表
当我们创建一个表时，Inceptor会将其创建在默认数据库default里。加上[db.name.]选项，Inceptor会将表创建在我们指定的数据库里，并且将该表的目录创建为指定数据库的子目录。
示例：

[$host]transwarp> CREATE TABLE our_platform.user_info (
                    name             STRING,
                    acc_num          STRING,
                    password         STRING,
                    citizen_id       STRING,
                    bank_acc         STRING,
                    reg_date         STRING,
                    acc_level        STRING
                  );

这样，名为user_info的表创建在了exchange_platform数据库中。为了达成同样的效果，也可以使用USE DATABASE，将当前使用数据库设置为exchange_platform，然后再创建表:

[$host]transwarp> USE DATABSE our_platform;
[$host]transwarp> CREATE TABLE user_info (
                    name             STRING,
                    acc_num          STRING,
                    password         STRING,
                    citizen_id       STRING,
                    bank_acc         STRING,
                    reg_date         STRING,
                    acc_level        STRING
                  );

创建外表

语法

CREATE EXTERNAL TABLE table_name (col1 data_type1, col2 data_type2, ...)
ROW FORMAT row_format
STORED AS file_format
LOCATION 'hdfs_path'

说明
ROW FORMAT和STORED AS子句指明表的存储格式。关于存储格式的细节请参考章节存储格式和SerDe。

举例

[$host] transwarp> CREATE EXTERNAL TABLE user_info_ext (name STRING, acc_num STRING,password STRING, citizen_id STRING, bank_acc STRING, reg_date STRING, acc_level STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION '/user/root/test';

CREATE TABLE...LIKE：

我们可以用CREATE TABLE...LIKE来拷贝一个已存在的表的描述(schema)，但不拷贝表中的数据。
注意
CREATE TABLE...LIKE将一张表的描述完全拷贝，表的描述包括的信息有列名、列值类型、列的注解、表的注解、使用的SerDe等等。是表的元数据
语法

CREATE TABLE table_name
  LIKE existing_table_or_view_name

举例

[$host]transwarp> CREATE TABLE empty_user_info
                    LIKE user_info;

上面的语句中，CREATE TABLE...LIKE创建了emtpy_user_info，它的描述和user_info完全相同，但是是空表。我们可以用DESCRIBE EXTENDED来查看user_info和empty_user_info的描述:

[$host]transwarp> DESCRIBE EXTENDED user_info;
name                    string                  None
acc_num                 string                  None
password                string                  None
citizen_id              string                  None
bank_acc                string                  None
reg_date                date                    None
acc_level               string                  None

Detailed Table Information      Table(tableName:user_info, dbName:exchange_platform, owner:yarn, createTime:1417633254, lastAccessTime:0, retention:0, sd:StorageDescriptor(cols:[FieldSchema(name:name, type:string, comment:null), FieldSchema(name:acc_num, type:string, comment:null), FieldSchema(name:password, type:string, comment:null), FieldSchema(name:citizen_id, type:string, comment:null), FieldSchema(name:bank_acc, type:string, comment:null), FieldSchema(name:reg_date, type:date, comment:null), FieldSchema(name:acc_level, type:string, comment:null)], location:hdfs://ns/inceptor1/user/hive/warehouse/exchange_platform.db/user_info, inputFormat:org.apache.hadoop.mapred.TextInputFormat, outputFormat:org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat, compressed:false, numBuckets:-1, serdeInfo:SerDeInfo(name:null, serializationLib:org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, parameters:{serialization.format=1}), bucketCols:[], sortCols:[], parameters:{}, skewedInfo:SkewedInfo(skewedColNames:[], skewedColValues:[], skewedColValueLocationMaps:{}), storedAsSubDirectories:false), partitionKeys:[], parameters:{transient_lastDdlTime=1417633254}, viewOriginalText:null, viewExpandedText:null, tableType:MANAGED_TABLE, isRangePartitioned:false)
Time taken: 0.3 seconds

[$host] transwarp> describe extended empty_user_info;
name                    string                  None
acc_num                 string                  None
password                string                  None
citizen_id              string                  None
bank_acc                string                  None
reg_date                date                    None
acc_level               string                  None

Detailed Table Information      Table(tableName:empty_user_info, dbName:exchange_platform, owner:yarn, createTime:1417633303, lastAccessTime:0, retention:0, sd:StorageDescriptor(cols:[FieldSchema(name:name, type:string, comment:null), FieldSchema(name:acc_num, type:string, comment:null), FieldSchema(name:password, type:string, comment:null), FieldSchema(name:citizen_id, type:string, comment:null), FieldSchema(name:bank_acc, type:string, comment:null), FieldSchema(name:reg_date, type:date, comment:null), FieldSchema(name:acc_level, type:string, comment:null)], location:hdfs://ns/inceptor1/user/hive/warehouse/exchange_platform.db/empty_user_info, inputFormat:org.apache.hadoop.mapred.TextInputFormat, outputFormat:org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat, compressed:false, numBuckets:-1, serdeInfo:SerDeInfo(name:null, serializationLib:org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, parameters:{serialization.format=1}), bucketCols:[], sortCols:[], parameters:{}, skewedInfo:SkewedInfo(skewedColNames:[], skewedColValues:[], skewedColValueLocationMaps:{}), storedAsSubDirectories:false), partitionKeys:[], parameters:{transient_lastDdlTime=1417633303}, viewOriginalText:null, viewExpandedText:null, tableType:MANAGED_TABLE, isRangePartitioned:false)
Time taken: 0.268 seconds

可以看出，两张表的描述除了表名和创建时间不同外，其他都相同。

CREATE TABLE...AS SELECT (CTAS)

用CTAS我们可创建一个表来存放一次查询的结果。如果需要经常使用某个查询的结果，可以用CTAS将查询结果存储起来，调用查询结果时就不需要重复计算了。
语法：

[$host]transwarp> CREATE TABLE table_name AS SELECT select_statement;

使用CTAS创建的表是原子的(atomic), 意思是在这张表的内容填写完成之前，其他用户都看不到这张表。所以其他用户只能看到填写完成的表，否则就完全看不到表。一个CTAS语句包含两个部分，CREATE部分和SELECT部分。SELECT部分可以是任何SELECT语句，而CREATE部分则将SELECT部分得到的查询结果填入新创建的表中。使用CTAS时我们可以使用直接定义列创建表语句中不直接提及列的选项，比如SerDe和存储格式。 CTAS创建的表不可以分区，分桶，也不能是外部表。

示例

我们现在用股票交易平台的用户信息表做为例子。用户信息表中含有帐户密码，身份证号码和银行帐户等会影响帐户安全的信息。这些信息我们并不希望所有人都看到，所以现在我们创建一个不需要保密的用户信息表，表中不包含用户密码，身份证号码和银行帐户信息。

[$host]transwarp> CREATE TABLE nonsecure_user_info as SELECT name, acc_num, reg_date, acc_level from user_info;

user_info表：

[$host]transwarp> select * from user_info;
马从筠  6513065 115591  140400198711012307      96080357291141  20110101        A
祝韩恒  6670192 205239  230801197908126178      73790369990971  20100101        C
华微    5224133 531547  420529198911075631      32638281095907  20080214        B
魏向卉  3912384 841242  522632199301029404      68537153578048  20091202        A
宁新瑶  4580952 986634  420822199001119507      97711008856576  20081031        D
邱坤    0700735 737297  340811199211252278      14388242322818  20121024        A
李平    8725869 600709  460100198902070313      43081307046984  20130702        E
潘营泽  6600641 990590  511521198705077435      48471135593608  20110430        C
李韩瑶  2755506 015859  310230197912126559      42412396242237  20110916        D
管淑艳  2394923 783438  330683198005210864      99913863445174  20141003        C

nonsecure_user_info表：

[$host]transwarp> select * from nonsecure_user_info;
马从筠  6513065 20110101        A
祝韩恒  6670192 20100101        C
华微    5224133 20080214        B
魏向卉  3912384 20091202        A
宁新瑶  4580952 20081031        D
邱坤    0700735 20121024        A
李平    8725869 20130702        E
潘营泽  6600641 20110430        C
李韩瑶  2755506 20110916        D
管淑艳  2394923 20141003        C

创建内存表

Inceptor支持建立内存表，内存表中的数据会在机器运行时一直存储在内存中，所以将一些常用查询结果存储在内存表内可以大大提高计算速度。Inceptor提供checkpoint机制，将计算数据同步写入HDFS中，可以保证在存储了内存表的机器当机时，内存表中的数据可以从HDFS中直接读取恢复而不需要重新进行查询计算。内存表可以用两种方式创建：

通过CTAS在建表，建表时数据即填入。这种情况下，内存表不能分区或者分桶。

建空内存表，此时内存表可以分区分桶。之后可以通过INSERT INTO SELECT来插入数据。关于插入数据请参见“插入数据：INSERT”章节。

语法：通过CTAS建表

CREATE TABLE table_name TBLPROPERTIES ("cache" = "cache_medium", "cache.checkpoint"="true|false", ["filters"="filter_type"]) AS SELECT select_statment

语法：建空表，然后插入数据

CREATE TABLE table_name (column_name data_type, column_name data_type...) PARTITIONED BY (partition_key data_type, partition_key data_type) CLUSTERED BY (cluster_key, cluster_key, ...) INTO n BUCKETS TBLPROPERTIES ("cache" = "cache_medium", "cache.checkpoint"="true|false", ["filters"="filter_type"])

说明

"cache" = "cache_medium"指定计算缓存的介质。cache_medium可以选择ram，SSD和memeory。只有当服务器上配置有SSD时，才可以选择SSD作为缓存，Inceptor会自动利用SSD为计算加速。
"cache.checkpoint" = "true|false"指定是否设置checkpoint。如果设置checkpoint，查询结果会被同步放入HDFS中，在存储了内存表的机器当机时，内存表中的数据可以从HDFS中直接读取恢复而不需要重新进行查询计算。
"filters"="filter_type"为可选项，它指定一个过滤器。利用过滤器可以为某些查询进行优化。过滤器和利用过滤器的优化将在优化章节介绍。

举例
下例建一个内存表，存放所有账户级别为A的用户的姓名，并且选择将结果同步到HDFS：

[$host] transwarp> CREATE TABEL name_A TBLPROPERTIES ("cache" = "ram","checkpoint" = "true") AS SELECT name FROM user_info WHERE acc_level = 'A';

举例
下例建一个空表

DROP TABLE语句

DROP TABLE删除一个指定名称的表。
语法

[$host]transwarp> DROP TABLE table_name;

当被删除的表是托管表时，表的元数据和表中数据都会被删除。如果被删除的表是外部表，则只有它的元数据会被删除。

TRUNCATE TABLE语句

TRUNCATE TABLE删除表或者分区中的数据，但不删除表或分区的元数据。这个操作只能用于托管表。
语法
将一张指定表下的数据全部删除(对分区表和非分区表都适用）:

TRUNCATE TABLE table_name

将一张指定表下的指定分区中的数据全部删除：

TRUNCATE TABLE table_name [PARTITION partition_spec]

partition_spec:
  : (partition_column = partition_col_value, partition_column = partition_col_value, ...)

举例
删除分区表partition_user_info中acc_level为A的记录：

[$host] transwarp> SELECT * FROM partition_user_info;
李平    8725869 600709  460100198902070313      43081307046984  20130702        E
华微    5224133 531547  420529198911075631      32638281095907  20080214        B
祝韩恒  6670192 205239  230801197908126178      73790369990971  20100101        C
潘营泽  6600641 990590  511521198705077435      48471135593608  20110430        C
管淑艳  2394923 783438  330683198005210864      99913863445174  20141003        C
马从筠  6513065 115591  140400198711012307      96080357291141  20110101        A
魏向卉  3912384 841242  522632199301029404      68537153578048  20091202        A
邱坤    0700735 737297  340811199211252278      14388242322818  20121024        A
宁新瑶  4580952 986634  420822199001119507      97711008856576  20081031        D
李韩瑶  2755506 015859  310230197912126559      42412396242237  20110916        D

[$host] transwarp> TRUNCATE TABLE partition_user_info PARTITION (acc_level='A');
[$host] transwarp> SELECT * FROM partition_user_info;
李平    8725869 600709  460100198902070313      43081307046984  20130702        E
华微    5224133 531547  420529198911075631      32638281095907  20080214        B
祝韩恒  6670192 205239  230801197908126178      73790369990971  20100101        C
潘营泽  6600641 990590  511521198705077435      48471135593608  20110430        C
管淑艳  2394923 783438  330683198005210864      99913863445174  20141003        C
宁新瑶  4580952 986634  420822199001119507      97711008856576  20081031        D
李韩瑶  2755506 015859  310230197912126559      42412396242237  20110916        D

删除所有partition_user_info中的数据(partition_user_info将成为空表）：

[$host]transwarp> TRUNCATE TABLE partition_user_info;