CREATE-TABLE-AS-SELECT
Name
CREATE TABLE AS SELECT
Description
This statement creates the table structure by returning the results from the Select statement and imports the data at the same time
grammar:
CREATE TABLE table_name [( column_name_list )]
opt_engine:engineName
opt_keys:keys
opt_comment:tableComment
opt_partition:partition
opt_distribution:distribution
opt_rollup:index
opt_properties:tblProperties
opt_ext_properties:extProperties
KW_AS query_stmt:query_def
illustrate:
- The user needs to have
SELECT
permission for the source table andCREATE
permission for the target database - After a table is created, data is imported. If the import fails, the table is deleted
- You can specify the key type. The default key type is
Duplicate Key
Example
Using the field names in the SELECT statement
create table `test`.`select_varchar`
PROPERTIES(\"replication_num\" = \"1\")
as select * from `test`.`varchar_table`
Custom field names (need to match the number of fields returned)
create table `test`.`select_name`(user, testname, userstatus)
PROPERTIES(\"replication_num\" = \"1\")
as select vt.userId, vt.username, jt.status
from `test`.`varchar_table` vt join
`test`.`join_table` jt on vt.userId=jt.userId
Keywords
CREATE, TABLE, AS, SELECT