How to create table under a schema in a database

Question

I am trying to create a database-table like sql, where we have database, under it schema then table under schema, so that fully qualified name of my table becomes database.schema.tablename.

I am seeing if something similar can be done in databricks for un-managed tables and if it is recommended approach.

I tried :create database mydb location" which created database, but not sure how to create schema under it. If i do "create schema myschema" it is showing as a new database in databricks UI.

Answer 1

In Spark the create database syntax looks like this (see also the Databricks documentation):

CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] db_name
  [COMMENT comment_text]
  [LOCATION path]
  [WITH DBPROPERTIES (key1=val1, key2=val2, ...)]

As you can see there is no concept of a schema in a database. You can use create schema or create database to create a schema what in my opinion is the better term as you only create a schema definition. Data is stored in the defined location. You could have multiple table definitions pointing to the same location with un-managed tables.