| -- SORT_QUERY_RESULTS |
| --! qt:dataset:alltypesorc |
| |
| set hive.support.concurrency=true; |
| set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager; |
| set hive.acid.direct.insert.enabled=true; |
| set metastore.metadata.transformer.class=org.apache.hadoop.hive.metastore.MetastoreDefaultTransformer; |
| set hive.metastore.client.capabilities=HIVEFULLACIDREAD,HIVEFULLACIDWRITE,HIVECACHEINVALIDATE,HIVEMANAGESTATS,HIVEMANAGEDINSERTWRITE,HIVEMANAGEDINSERTREAD; |
| |
| DROP TABLE IF EXISTS source; |
| |
| DROP TABLE IF EXISTS test_orc_ctas; |
| |
| DROP TABLE IF EXISTS test_orc_mmctas; |
| |
| DROP TABLE IF EXISTS test_parquet_mmctas; |
| |
| DROP TABLE IF EXISTS test_avro_mmctas; |
| |
| DROP TABLE IF EXISTS test_textfile_mmctas; |
| |
| DROP TABLE IF EXISTS test_partition_orc_ctas; |
| |
| DROP TABLE IF EXISTS test_partition_orc_mmctas; |
| |
| DROP TABLE IF EXISTS test_partition_parquet_mmctas; |
| |
| DROP TABLE IF EXISTS test_partition_avro_mmctas; |
| |
| DROP TABLE IF EXISTS test_partition_textfile_mmctas; |
| |
| CREATE TABLE source STORED AS ORC TBLPROPERTIES('transactional'='true') AS (SELECT cint, cfloat, cdouble, cstring1, ctimestamp1 FROM alltypesorc); |
| |
| CREATE TABLE test_orc_ctas STORED AS ORC TBLPROPERTIES('transactional'='true') AS ((SELECT * FROM alltypesorc WHERE cint > 200 LIMIT 10) UNION (SELECT * FROM alltypesorc WHERE cint < -100 LIMIT 10)); |
| |
| CREATE TABLE test_orc_mmctas STORED AS ORC TBLPROPERTIES('transactional'='true', 'transactional_properties'='insert_only') AS ((SELECT * FROM alltypesorc WHERE cint > 200 LIMIT 10) UNION (SELECT * FROM alltypesorc WHERE cint < -100 LIMIT 10)); |
| |
| CREATE TABLE test_parquet_mmctas STORED AS PARQUET TBLPROPERTIES('transactional'='true', 'transactional_properties'='insert_only') AS ((SELECT * FROM alltypesorc WHERE cint > 200 LIMIT 10) UNION (SELECT * FROM alltypesorc WHERE cint < -100 LIMIT 10)); |
| |
| CREATE TABLE test_avro_mmctas STORED AS AVRO TBLPROPERTIES('transactional'='true', 'transactional_properties'='insert_only') AS ((SELECT * FROM source WHERE cint > 200 LIMIT 10) UNION (SELECT * FROM source WHERE cint < -100 LIMIT 10)); |
| |
| CREATE TABLE test_textfile_mmctas STORED AS TEXTFILE TBLPROPERTIES('transactional'='true', 'transactional_properties'='insert_only') AS ((SELECT * FROM alltypesorc WHERE cint > 200 LIMIT 10) UNION (SELECT * FROM alltypesorc WHERE cint < -100 LIMIT 10)); |
| |
| CREATE TABLE test_partition_orc_ctas PARTITIONED BY (cint) STORED AS ORC TBLPROPERTIES('transactional'='true') AS ((SELECT * FROM alltypesorc WHERE cint > 200 LIMIT 10) UNION (SELECT * FROM alltypesorc WHERE cint < -100 LIMIT 10)); |
| |
| CREATE TABLE test_partition_orc_mmctas PARTITIONED BY (cint) STORED AS ORC TBLPROPERTIES('transactional'='true', 'transactional_properties'='insert_only') AS ((SELECT * FROM alltypesorc WHERE cint > 200 LIMIT 10) UNION (SELECT * FROM alltypesorc WHERE cint < -100 LIMIT 10)); |
| |
| CREATE TABLE test_partition_parquet_mmctas PARTITIONED BY (cint) STORED AS PARQUET TBLPROPERTIES('transactional'='true', 'transactional_properties'='insert_only') AS ((SELECT * FROM alltypesorc WHERE cint > 200 LIMIT 10) UNION (SELECT * FROM alltypesorc WHERE cint < -100 LIMIT 10)); |
| |
| CREATE TABLE test_partition_avro_mmctas PARTITIONED BY (cint) STORED AS AVRO TBLPROPERTIES('transactional'='true', 'transactional_properties'='insert_only') AS ((SELECT * FROM source WHERE cint > 200 LIMIT 10) UNION (SELECT * FROM source WHERE cint < -100 LIMIT 10)); |
| |
| CREATE TABLE test_partition_textfile_mmctas PARTITIONED BY (cint) STORED AS TEXTFILE TBLPROPERTIES('transactional'='true', 'transactional_properties'='insert_only') AS ((SELECT * FROM alltypesorc WHERE cint > 200 LIMIT 10) UNION (SELECT * FROM alltypesorc WHERE cint < -100 LIMIT 10)); |
| |
| CREATE TRANSACTIONAL TABLE test_transactional_orc_ctas STORED AS ORC AS ((SELECT * FROM alltypesorc WHERE cint > 200 LIMIT 10) UNION (SELECT * FROM alltypesorc WHERE cint < -100 LIMIT 10)); |
| |
| CREATE TRANSACTIONAL TABLE test_transactional_part_orc_ctas PARTITIONED BY (cint) STORED AS ORC AS ((SELECT * FROM alltypesorc WHERE cint > 200 LIMIT 10) UNION (SELECT * FROM alltypesorc WHERE cint < -100 LIMIT 10)); |
| |
| CREATE MANAGED TABLE test_managed_orc_ctas STORED AS ORC AS ((SELECT * FROM alltypesorc WHERE cint > 200 LIMIT 10) UNION (SELECT * FROM alltypesorc WHERE cint < -100 LIMIT 10)); |
| |
| CREATE MANAGED TABLE test_managed_part_orc_ctas PARTITIONED BY (cint) STORED AS ORC AS ((SELECT * FROM alltypesorc WHERE cint > 200 LIMIT 10) UNION (SELECT * FROM alltypesorc WHERE cint < -100 LIMIT 10)); |
| |
| SELECT * FROM test_orc_ctas ORDER BY cint; |
| |
| SELECT * FROM test_orc_mmctas ORDER BY cint; |
| |
| SELECT * FROM test_parquet_mmctas ORDER BY cint; |
| |
| SELECT * FROM test_avro_mmctas ORDER BY cint; |
| |
| SELECT * FROM test_textfile_mmctas ORDER BY cint; |
| |
| SELECT * FROM test_partition_orc_ctas ORDER BY cint; |
| |
| SELECT * FROM test_partition_orc_mmctas ORDER BY cint; |
| |
| SELECT * FROM test_partition_parquet_mmctas ORDER BY cint; |
| |
| SELECT * FROM test_partition_avro_mmctas ORDER BY cint; |
| |
| SELECT * FROM test_partition_textfile_mmctas ORDER BY cint; |
| |
| SELECT * FROM test_transactional_orc_ctas; |
| |
| SELECT * FROM test_transactional_part_orc_ctas; |
| |
| SELECT * FROM test_managed_orc_ctas; |
| |
| SELECT * FROM test_managed_part_orc_ctas; |
| |
| DROP TABLE IF EXISTS source; |
| |
| DROP TABLE IF EXISTS test_orc_ctas; |
| |
| DROP TABLE IF EXISTS test_orc_mmctas; |
| |
| DROP TABLE IF EXISTS test_parquet_mmctas; |
| |
| DROP TABLE IF EXISTS test_avro_mmctas; |
| |
| DROP TABLE IF EXISTS test_textfile_mmctas; |
| |
| DROP TABLE IF EXISTS test_partition_orc_ctas; |
| |
| DROP TABLE IF EXISTS test_partition_orc_mmctas; |
| |
| DROP TABLE IF EXISTS test_partition_parquet_mmctas; |
| |
| DROP TABLE IF EXISTS test_partition_avro_mmctas; |
| |
| DROP TABLE IF EXISTS test_partition_textfile_mmctas; |
| |
| DROP TABLE IF EXISTS test_transactional_orc_ctas; |
| |
| DROP TABLE IF EXISTS test_transactional_part_orc_ctas; |
| |
| DROP TABLE IF EXISTS test_managed_orc_ctas; |
| |
| DROP TABLE IF EXISTS test_managed_part_orc_ctas; |
| |
| -- Use specified locations |
| |
| CREATE TABLE source STORED AS ORC LOCATION '/build/ql/test/data/warehouse/source1' TBLPROPERTIES('transactional'='true') AS (SELECT cint, cfloat, cdouble, cstring1, ctimestamp1 FROM alltypesorc); |
| |
| CREATE TABLE test_orc_ctas STORED AS ORC LOCATION '/build/ql/test/data/warehouse/test_orc' TBLPROPERTIES('transactional'='true') AS ((SELECT * FROM alltypesorc WHERE cint > 200 LIMIT 10) UNION (SELECT * FROM alltypesorc WHERE cint < -100 LIMIT 10)); |
| |
| CREATE TABLE test_orc_mmctas STORED AS ORC LOCATION '/build/ql/test/data/warehouse/test_mm_orc' TBLPROPERTIES('transactional'='true', 'transactional_properties'='insert_only') AS ((SELECT * FROM alltypesorc WHERE cint > 200 LIMIT 10) UNION (SELECT * FROM alltypesorc WHERE cint < -100 LIMIT 10)); |
| |
| CREATE TABLE test_parquet_mmctas STORED AS PARQUET LOCATION '/build/ql/test/data/warehouse/test_mm_parquet' TBLPROPERTIES('transactional'='true', 'transactional_properties'='insert_only') AS ((SELECT * FROM alltypesorc WHERE cint > 200 LIMIT 10) UNION (SELECT * FROM alltypesorc WHERE cint < -100 LIMIT 10)); |
| |
| CREATE TABLE test_avro_mmctas STORED AS AVRO LOCATION '/build/ql/test/data/warehouse/test_mm_avro' TBLPROPERTIES('transactional'='true', 'transactional_properties'='insert_only') AS ((SELECT * FROM source WHERE cint > 200 LIMIT 10) UNION (SELECT * FROM source WHERE cint < -100 LIMIT 10)); |
| |
| CREATE TABLE test_textfile_mmctas STORED AS TEXTFILE LOCATION '/build/ql/test/data/warehouse/test_mm_textfile' TBLPROPERTIES('transactional'='true', 'transactional_properties'='insert_only') AS ((SELECT * FROM alltypesorc WHERE cint > 200 LIMIT 10) UNION (SELECT * FROM alltypesorc WHERE cint < -100 LIMIT 10)); |
| |
| CREATE TABLE test_partition_orc_ctas PARTITIONED BY (cint) STORED AS ORC LOCATION '/build/ql/test/data/warehouse/test_part_orc' TBLPROPERTIES('transactional'='true') AS ((SELECT * FROM alltypesorc WHERE cint > 200 LIMIT 10) UNION (SELECT * FROM alltypesorc WHERE cint < -100 LIMIT 10)); |
| |
| CREATE TABLE test_partition_orc_mmctas PARTITIONED BY (cint) STORED AS ORC LOCATION '/build/ql/test/data/warehouse/test_mm_part_orc' TBLPROPERTIES('transactional'='true', 'transactional_properties'='insert_only') AS ((SELECT * FROM alltypesorc WHERE cint > 200 LIMIT 10) UNION (SELECT * FROM alltypesorc WHERE cint < -100 LIMIT 10)); |
| |
| CREATE TABLE test_partition_parquet_mmctas PARTITIONED BY (cint) STORED AS PARQUET LOCATION '/build/ql/test/data/warehouse/test_mm_part_parquet' TBLPROPERTIES('transactional'='true', 'transactional_properties'='insert_only') AS ((SELECT * FROM alltypesorc WHERE cint > 200 LIMIT 10) UNION (SELECT * FROM alltypesorc WHERE cint < -100 LIMIT 10)); |
| |
| CREATE TABLE test_partition_avro_mmctas PARTITIONED BY (cint) STORED AS AVRO LOCATION '/build/ql/test/data/warehouse/test_mm_part_avro' TBLPROPERTIES('transactional'='true', 'transactional_properties'='insert_only') AS ((SELECT * FROM source WHERE cint > 200 LIMIT 10) UNION (SELECT * FROM source WHERE cint < -100 LIMIT 10)); |
| |
| CREATE TABLE test_partition_textfile_mmctas PARTITIONED BY (cint) STORED AS TEXTFILE LOCATION '/build/ql/test/data/warehouse/test_mm_part_textfile' TBLPROPERTIES('transactional'='true', 'transactional_properties'='insert_only') AS ((SELECT * FROM alltypesorc WHERE cint > 200 LIMIT 10) UNION (SELECT * FROM alltypesorc WHERE cint < -100 LIMIT 10)); |
| |
| CREATE TRANSACTIONAL TABLE test_transactional_orc_ctas STORED AS ORC LOCATION '/build/ql/test/data/warehouse/test_transactional_orc' AS ((SELECT * FROM alltypesorc WHERE cint > 200 LIMIT 10) UNION (SELECT * FROM alltypesorc WHERE cint < -100 LIMIT 10)); |
| |
| CREATE TRANSACTIONAL TABLE test_transactional_part_orc_ctas PARTITIONED BY (cint) STORED AS ORC LOCATION '/build/ql/test/data/warehouse/test_transactional_part_orc' AS ((SELECT * FROM alltypesorc WHERE cint > 200 LIMIT 10) UNION (SELECT * FROM alltypesorc WHERE cint < -100 LIMIT 10)); |
| |
| CREATE MANAGED TABLE test_managed_orc_ctas STORED AS ORC LOCATION '/build/ql/test/data/warehouse/test_managed_orc' AS ((SELECT * FROM alltypesorc WHERE cint > 200 LIMIT 10) UNION (SELECT * FROM alltypesorc WHERE cint < -100 LIMIT 10)); |
| |
| CREATE MANAGED TABLE test_managed_part_orc_ctas PARTITIONED BY (cint) STORED AS ORC LOCATION '/build/ql/test/data/warehouse/test_managed_part_orc' AS ((SELECT * FROM alltypesorc WHERE cint > 200 LIMIT 10) UNION (SELECT * FROM alltypesorc WHERE cint < -100 LIMIT 10)); |
| |
| SELECT * FROM test_orc_ctas ORDER BY cint; |
| |
| SELECT * FROM test_orc_mmctas ORDER BY cint; |
| |
| SELECT * FROM test_parquet_mmctas ORDER BY cint; |
| |
| SELECT * FROM test_avro_mmctas ORDER BY cint; |
| |
| SELECT * FROM test_textfile_mmctas ORDER BY cint; |
| |
| SELECT * FROM test_partition_orc_ctas ORDER BY cint; |
| |
| SELECT * FROM test_partition_orc_mmctas ORDER BY cint; |
| |
| SELECT * FROM test_partition_parquet_mmctas ORDER BY cint; |
| |
| SELECT * FROM test_partition_avro_mmctas ORDER BY cint; |
| |
| SELECT * FROM test_partition_textfile_mmctas ORDER BY cint; |
| |
| SELECT * FROM test_transactional_orc_ctas; |
| |
| SELECT * FROM test_transactional_part_orc_ctas; |
| |
| SELECT * FROM test_managed_orc_ctas; |
| |
| SELECT * FROM test_managed_part_orc_ctas; |
| |
| DROP TABLE IF EXISTS source; |
| |
| DROP TABLE IF EXISTS test_orc_ctas; |
| |
| DROP TABLE IF EXISTS test_orc_mmctas; |
| |
| DROP TABLE IF EXISTS test_parquet_mmctas; |
| |
| DROP TABLE IF EXISTS test_avro_mmctas; |
| |
| DROP TABLE IF EXISTS test_textfile_mmctas; |
| |
| DROP TABLE IF EXISTS test_partition_orc_ctas; |
| |
| DROP TABLE IF EXISTS test_partition_orc_mmctas; |
| |
| DROP TABLE IF EXISTS test_partition_parquet_mmctas; |
| |
| DROP TABLE IF EXISTS test_partition_avro_mmctas; |
| |
| DROP TABLE IF EXISTS test_partition_textfile_mmctas; |
| |
| DROP TABLE IF EXISTS test_transactional_orc_ctas; |
| |
| DROP TABLE IF EXISTS test_transactional_part_orc_ctas; |
| |
| DROP TABLE IF EXISTS test_managed_orc_ctas; |
| |
| DROP TABLE IF EXISTS test_managed_part_orc_ctas; |
| |
| -- Use suffixing in table locations |
| set hive.acid.createtable.softdelete=true; |
| |
| CREATE TABLE source STORED AS ORC TBLPROPERTIES('transactional'='true') AS (SELECT cint, cfloat, cdouble, cstring1, ctimestamp1 FROM alltypesorc); |
| |
| CREATE TABLE test_orc_ctas STORED AS ORC TBLPROPERTIES('transactional'='true') AS ((SELECT * FROM alltypesorc WHERE cint > 200 LIMIT 10) UNION (SELECT * FROM alltypesorc WHERE cint < -100 LIMIT 10)); |
| |
| CREATE TABLE test_orc_mmctas STORED AS ORC TBLPROPERTIES('transactional'='true', 'transactional_properties'='insert_only') AS ((SELECT * FROM alltypesorc WHERE cint > 200 LIMIT 10) UNION (SELECT * FROM alltypesorc WHERE cint < -100 LIMIT 10)); |
| |
| CREATE TABLE test_parquet_mmctas STORED AS PARQUET TBLPROPERTIES('transactional'='true', 'transactional_properties'='insert_only') AS ((SELECT * FROM alltypesorc WHERE cint > 200 LIMIT 10) UNION (SELECT * FROM alltypesorc WHERE cint < -100 LIMIT 10)); |
| |
| CREATE TABLE test_avro_mmctas STORED AS AVRO TBLPROPERTIES('transactional'='true', 'transactional_properties'='insert_only') AS ((SELECT * FROM source WHERE cint > 200 LIMIT 10) UNION (SELECT * FROM source WHERE cint < -100 LIMIT 10)); |
| |
| CREATE TABLE test_textfile_mmctas STORED AS TEXTFILE TBLPROPERTIES('transactional'='true', 'transactional_properties'='insert_only') AS ((SELECT * FROM alltypesorc WHERE cint > 200 LIMIT 10) UNION (SELECT * FROM alltypesorc WHERE cint < -100 LIMIT 10)); |
| |
| CREATE TABLE test_partition_orc_ctas PARTITIONED BY (cint) STORED AS ORC TBLPROPERTIES('transactional'='true') AS ((SELECT * FROM alltypesorc WHERE cint > 200 LIMIT 10) UNION (SELECT * FROM alltypesorc WHERE cint < -100 LIMIT 10)); |
| |
| CREATE TABLE test_partition_orc_mmctas PARTITIONED BY (cint) STORED AS ORC TBLPROPERTIES('transactional'='true', 'transactional_properties'='insert_only') AS ((SELECT * FROM alltypesorc WHERE cint > 200 LIMIT 10) UNION (SELECT * FROM alltypesorc WHERE cint < -100 LIMIT 10)); |
| |
| CREATE TABLE test_partition_parquet_mmctas PARTITIONED BY (cint) STORED AS PARQUET TBLPROPERTIES('transactional'='true', 'transactional_properties'='insert_only') AS ((SELECT * FROM alltypesorc WHERE cint > 200 LIMIT 10) UNION (SELECT * FROM alltypesorc WHERE cint < -100 LIMIT 10)); |
| |
| CREATE TABLE test_partition_avro_mmctas PARTITIONED BY (cint) STORED AS AVRO TBLPROPERTIES('transactional'='true', 'transactional_properties'='insert_only') AS ((SELECT * FROM source WHERE cint > 200 LIMIT 10) UNION (SELECT * FROM source WHERE cint < -100 LIMIT 10)); |
| |
| CREATE TABLE test_partition_textfile_mmctas PARTITIONED BY (cint) STORED AS TEXTFILE TBLPROPERTIES('transactional'='true', 'transactional_properties'='insert_only') AS ((SELECT * FROM alltypesorc WHERE cint > 200 LIMIT 10) UNION (SELECT * FROM alltypesorc WHERE cint < -100 LIMIT 10)); |
| |
| CREATE TRANSACTIONAL TABLE test_transactional_orc_ctas STORED AS ORC AS ((SELECT * FROM alltypesorc WHERE cint > 200 LIMIT 10) UNION (SELECT * FROM alltypesorc WHERE cint < -100 LIMIT 10)); |
| |
| CREATE TRANSACTIONAL TABLE test_transactional_part_orc_ctas PARTITIONED BY (cint) STORED AS ORC AS ((SELECT * FROM alltypesorc WHERE cint > 200 LIMIT 10) UNION (SELECT * FROM alltypesorc WHERE cint < -100 LIMIT 10)); |
| |
| CREATE MANAGED TABLE test_managed_orc_ctas STORED AS ORC AS ((SELECT * FROM alltypesorc WHERE cint > 200 LIMIT 10) UNION (SELECT * FROM alltypesorc WHERE cint < -100 LIMIT 10)); |
| |
| CREATE MANAGED TABLE test_managed_part_orc_ctas PARTITIONED BY (cint) STORED AS ORC AS ((SELECT * FROM alltypesorc WHERE cint > 200 LIMIT 10) UNION (SELECT * FROM alltypesorc WHERE cint < -100 LIMIT 10)); |
| |
| SELECT * FROM test_orc_ctas ORDER BY cint; |
| |
| SELECT * FROM test_orc_mmctas ORDER BY cint; |
| |
| SELECT * FROM test_parquet_mmctas ORDER BY cint; |
| |
| SELECT * FROM test_avro_mmctas ORDER BY cint; |
| |
| SELECT * FROM test_textfile_mmctas ORDER BY cint; |
| |
| SELECT * FROM test_partition_orc_ctas ORDER BY cint; |
| |
| SELECT * FROM test_partition_orc_mmctas ORDER BY cint; |
| |
| SELECT * FROM test_partition_parquet_mmctas ORDER BY cint; |
| |
| SELECT * FROM test_partition_avro_mmctas ORDER BY cint; |
| |
| SELECT * FROM test_partition_textfile_mmctas ORDER BY cint; |
| |
| SELECT * FROM test_transactional_orc_ctas; |
| |
| SELECT * FROM test_transactional_part_orc_ctas; |
| |
| SELECT * FROM test_managed_orc_ctas; |
| |
| SELECT * FROM test_managed_part_orc_ctas; |
| |
| DROP TABLE IF EXISTS source; |
| |
| DROP TABLE IF EXISTS test_orc_ctas; |
| |
| DROP TABLE IF EXISTS test_orc_mmctas; |
| |
| DROP TABLE IF EXISTS test_parquet_mmctas; |
| |
| DROP TABLE IF EXISTS test_avro_mmctas; |
| |
| DROP TABLE IF EXISTS test_textfile_mmctas; |
| |
| DROP TABLE IF EXISTS test_partition_orc_ctas; |
| |
| DROP TABLE IF EXISTS test_partition_orc_mmctas; |
| |
| DROP TABLE IF EXISTS test_partition_parquet_mmctas; |
| |
| DROP TABLE IF EXISTS test_partition_avro_mmctas; |
| |
| DROP TABLE IF EXISTS test_partition_textfile_mmctas; |
| |
| DROP TABLE IF EXISTS test_transactional_orc_ctas; |
| |
| DROP TABLE IF EXISTS test_transactional_part_orc_ctas; |
| |
| DROP TABLE IF EXISTS test_managed_orc_ctas; |
| |
| DROP TABLE IF EXISTS test_managed_part_orc_ctas; |