CODEWITHSUNDEEP
apacheapache-drill
user6023611
user6023611

Reputation:

Apache Drill - creating own table by CTAS

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.

Upvotes: 1

Views: 4601

Answers (2)

Anamika Gupta
Anamika Gupta

Reputation: 1

You need to use dfs or any other storage plugin which uses local file system. eg : create table dfs.test.temp_count (user_count) as select t1.data as userParams from s3device.<File-Path> t1 limit 1

Upvotes: 0

Dev
Dev

Reputation: 13753

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.

Upvotes: 5

Related Questions