table – Programming & Mustangs!

Databricks Unity Catalog SQL Commands

This post is basic commands you will need to know for working with Unity Catalog.

Display Current Metastore

SELECT CURRENT_METASTORE();

Display Current Catalog

SELECT CURRENT_CATALOG();

Create Catalog

CREATE CATALOG IF NOT EXISTS  <Catalog_Name> COMMENT 'A COMMENT';

Create Catalog With Location

CREATE CATALOG IF NOT EXISTS <Catalog_Name> MANAGED LOCATION 'abfss://<METASTORE_CONTAINER_NAME>@<STORAGE_ACCOUNT>.dfs.core.windows.net/<CATALOG_NAME>' COMMENT 'A COMMENT';

Describe Catalog

DESCRIBE CATALOG <Catalog_Name>;

Create Schema

CREATE SCHEMA IF NOT EXISTS <SCHEMA_NAME> COMMENT '<COMMENT>';

Create Schema With Location

CREATE SCHEMA IF NOT EXISTS <SCHEMA_NAME> MANAGED LOCATION 'abfss://<METASTORE_CONTAINER_NAME>@<STORAGE_ACCOUNT>.dfs.core.windows.net/<CATALOG_NAME>/<SCHEMA_NAME>' COMMENT '<COMMENT>';

Show All Storage Credentials

SHOW STORAGE CREDENTIALS;

Describe Credential

DESCRIBE STORAGE CREDENTIAL <CREDENTIAL_NAME>;

Create External Location

You will first need a storage credential.

You can reference down to the full table path or keep it at the container

CREATE EXTERNAL LOCATION IF NOT EXISTS <NAME>
URL 'abfss://<COTNAINER>@<STORAGE_ACCOUNT>.dfs.core.windows.net/'
WITH (STORAGE CREDENTIAL <CREDENTIAL_NAME>)
COMMENT '<COMMENT>';

Create External Table

CREATE TABLE <CATALOG_NAME>.<SCHEMA_NAME>.<TABLE_NAME>
USING <FORMAT>
LOCATION 'abfss://<COTNAINER>@<STORAGE_ACCOUNT>.dfs.core.windows.net/FOLDER/PATH;'

Grant Create Storage Credential on Metastore

GRANT CREATE STORAGE CREDENTIAL ON METASTORE TO `<USER>`;

Grant Permission to Create External Locations on Storage Credential

GRANT CREATE EXTERNAL LOCATION ON STORAGE CREDENTIAL <CREDENTIAL_NAME> TO `<USER>`;

Grant Permission to Create External Location On Metastored

GRANT CREATE EXTERNAL LOCATION ON METASTORE TO `<USER>`;

Grant Permission to Use Catalog

GRANT USE_CATALOG ON CATALOG <CATALOG_NAME> TO `<USER>`;

Show all Grants On Metastore

SHOW GRANTS `<USER>` ON METASTORE;

Grant Permission to Use Schema

GRANT USE_SCHEMA ON SCHEMA <CATALOG_NAME>.<SCHEMA_NAME> TO `<USER>`;

Grant Permission to Create Table

GRANT CREATE TABLE ON SCHEMA <CATALOG_NAME>.<SCHEMA_NAME> TO <USER>;

Hive: Tables

This tutorial will show you some common usage for working with tables. If you have no installed Hive yet please follow this tutorial.

Show Tables:

SHOW TABLES;
SHOW TABLES LIKE '*test*';

Table Creation:

CREATE TABLE test (
    columnA STRING,
    columnB VARCHAR(15),
    columnC INT,
    columnD TIMESTAMP,
    columnE DATE
)
STORED AS ORC;

Table Creation with Partitioning:

CREATE TABLE test_partition (
    columnA STRING,
    columnB VARCHAR(15),
    columnC INT,
    columnD TIMESTAMP,
    columnE DATE
)
PARTITIONED BY (columnF INT)
STORED AS ORC;

Inline Table Creation:

CREATE TABLE test_inline STORED AS ORC AS
SELECT *
FROM test;

Temporary Table Creation:

CREATE TEMPORARY TABLE temp (
    columnA STRING,
    columnB VARCHAR(15),
    columnC INT,
    columnD TIMESTAMP,
    columnE DATE
)
STORED AS ORC;

DESC Table:
This will show you the basic definition of a table.

DESC test;

DESC EXTENDED Table:
This will show you the extended definition of a table.

DESC EXTENDED test;

Drop Table:

DROP TABLE IF EXISTS temp;

HBASE & Java: Delete a Table

This tutorial will guide you through how to delete a HBASE table using Java 8. Make sure you first follow this tutorial on connecting to HBASE.

Import:

import org.apache.hadoop.hbase.client.Admin;

Delete:

//You must first disable the table
conn.getAdmin().disableTable(TableName.valueOf("myTable"));
 
//Now you can delete the table
conn.getAdmin().deleteTable(TableName.valueOf("myTable"));

HBASE & Java: Search for Data

This tutorial will give you a quick overview of how to search for data using HBASE. If you have not done so yet. Follow the following two tutorials on HBASE: Connecting and HBASE: Create a Table.

Search for Data:

Basically we have to scan the table for data. So we must first setup a scan object then search for the data.

import org.apache.hadoop.hbase.client.Result;
import org.apache.hadoop.hbase.client.ResultScanner;
import org.apache.hadoop.hbase.client.Scan;
import org.apache.hadoop.hbase.Cell;
import org.apache.hadoop.hbase.client.Table;
import org.apache.hadoop.hbase.TableName;
import org.apache.hadoop.hbase.util.Bytes;
 
//Lets setup our scan object.
final Scan scan = new Scan();
//Search a particular column
scan.addColumn(Bytes.toBytes("columnFamily"), Bytes.toBytes("columnName"));
//Check the row key prefix
scan.setRowPrefixFilter(Bytes.toBytes("rowkey"));
 
final TableName table = TableName.valueOf(yourTableName);
 
//Get the table you want to work with. using the connection from the tutorial above.
final Table table = conn.getTable(table);
//Create our scanner based on the scan object above.
final ResultScanner scanner = table.getScanner(scan);
 
//Now we will loop through our results
for (Result result = scanner.next(); result != null; result = scanner.next()) {
      //Lets get our row key
      final String rowIdentifier = Bytes.toString(result.getRow());
 
      //Now based on each record found we will loop through the available cells for that record.
      for (final Cell cell : result.listCells()) {
        //now we can do whatever we need to with the data.
        log.info("column {} value {}", Bytes.toString(cell.getQualifierArray(), cell.getQualifierOffset(), cell.getQualifierLength()), Bytes.toString(cell.getValueArray(), cell.getValueOffset(), cell.getValueLength()));
      }
}

HBASE & Java: Create a Table

This tutorial will guide you through how to create a HBASE table using Java 8. Make sure you first follow this tutorial on connecting to HBASE.

Table Exists:

This checks if the table already exists in HBASE.

import org.apache.hadoop.hbase.TableName;
 
final TableName table = TableName.valueOf(yourTableName);
 
//Use the connection object to getAdmin from the connection tutorial above.
conn.getAdmin().tableExists(table);

Create Table:

In the most basic example of creating a HBASE table you need to know the name and the column families. A column family is columns grouped together. The data is related in some way and stored together on disk. Notice how we don’t define columns in the table design. Columns are added as we put data. Which I will give example below.

import org.apache.hadoop.hbase.HColumnDescriptor;
import org.apache.hadoop.hbase.HTableDescriptor;
import org.apache.hadoop.hbase.TableName;
 
final TableName table = TableName.valueOf(yourTableName);
 
final HTableDescriptor hTableBuilder = new HTableDescriptor(table);
final HColumnDescriptor column = new HColumnDescriptor(family);
hTableBuilder.addFamily(column);
 
//Use the connection object to getAdmin from the connection tutorial above.
conn.getAdmin().createTable(hTableBuilder);

Get a Table:

This will retrieve a table from HBASE so you can use it to put data, etc.

import org.apache.hadoop.hbase.TableName;
import org.apache.hadoop.hbase.client.Table;
 
final TableName table = TableName.valueOf(yourTableName);
 
//Use the connection object from the connection tutorial above.
final Table table = conn.getTable(table);

Put Data:

Now we will put data into the table we have reference to above. Notice how the columns are referenced.

import org.apache.hadoop.hbase.client.Put;
import org.apache.hadoop.hbase.util.Bytes;
 
final byte[] rowKey = Bytes.toBytes("some row identifier");
final byte[] columnFamily = Bytes.toBytes("myFamily");
final byte[] columnName = Bytes.toBytes("columnName");
final byte[] data = Bytes.toBytes(myData);
 
final Put put = new Put(rowKey);
put.addColumn(columnFamily, columnName, data);
 
//Insert the data.
table.put(put);
//Close the table.
table.close();