Apache Drill - creating own table by CTAS

Question

The CTAS commans is: CREATE TABLE new_table_name AS <query>;
However, before creating table I have to create schema. I can't do it, moreover I can't use existing schema because for each installed yet schema I get error:
schema cp is immutable. Hence solution is create new schema. The problem is that I can't find any example of this command.

Answer 1

Using CTAS, as per the docs you can only create new tables in workspaces. You cannot create tables in other storage plugins such as Hive and HBase.

You can store table data in one of three formats:

• csv
• parquet
• json

Steps to create table using CTAS:

1. Set store.format:

   alter session set `store.format`='json'; 
   

2. set location at which table (better to call file) is created:

   Go to dfs plugin: http://localhost:8047/storage/dfs

   In "workspaces", add writable (mutable) workspace. Eg:

    "tmp": {
        "location": "/tmp",
         "writable": true,
          } 
   

   do notice "writable": true

3. Use workspace:

   use dfs.tmp;
   

4. Fire CTAS command. Eg:

   CREATE TABLE new_table_name AS (SELECT * FROM hive.mytable);
   

Check /tmp/abc directory, you will find JSON file.