Search code examples
mysqlapache-drill

Create table in mysql db using apache drill

I tried creating a table in mysql db using apache drill. It throwed an error: schema is immutable. One workaround I figured out was to create a temporary table using CTAS statement. But I want to create a permanent table in mysql using drill. Please help.

Here is my MySQL plugin configuration:

{
  "type": "jdbc",
  "driver": "com.mysql.jdbc.Driver",
  "url": "jdbc:mysql://hostname:3306",
  "username": "root",
  "password": "",
  "enabled": true
}
Solution

  • Drill cannot create tables in MySql.

    Restrictions You can only create new tables in workspaces. You cannot create tables using storage plugins, such as Hive and HBase, that do not specify a workspace.

    You must use a writable (mutable) workspace when creating Drill tables.

    Temporary table that you have been able to create was created in file system not in MySql.

    By default, Drill creates temporary tables within the default temporary workspace, dfs.tmp. The default temporary workspace must be writable, file-based, and point to a location that already exists, otherwise temporary table creation fails. You cannot create a temporary table outside of the default temporary workspace.

    [1] https://drill.apache.org/docs/create-table-as-ctas/

    [2] https://drill.apache.org/docs/create-temporary-table-as-cttas/