How to create and use a new Spark database for the materialization#

This guide’s objective is to describe the actions required to create and use an alternative Spark database as a default storage for materialization on Spark.

Introduction and Prerequisites#

Users performing the steps need to have:

  • administrative access to Querona,

  • administrative access to the folder designated to store Spark’s database files.

Basic knowledge of SQL language is required.

Setup procedure#

Step 1, choose Spark connection and database location#

Connect to Querona’s administrative portal with administrative privileges.

Navigate to CONNECTIONS and copy the Spark connection name you want to use, for example, “Local Spark 3”.

Decide where on your filesystem you want Spark to store files (aka. database location) of the new database, for example, “d:/sparkdb”.

Step 2, create a database on Spark#

To create a database on Spark, we need to issue a native Spark command using the qua_execute_provider_sql_on_connection stored procedure.

Navigate to QUERY, and execute the the following code:

EXEC qua_execute_provider_sql_on_connection @connection_name = 'Local Spark 3', @sql='CREATE DATABASE sparkdb LOCATION ''d:/sparkdb''';

Command should complete successfully.

Step 3, change the default Spark database#

  1. Navigate to CONNECTIONS ‣ Local Spark 3, and click Edit to edit the connection.

  2. In the “Default database” drop-down, select the newly created database.

  3. Click Save.

From now on, all newly enabled materialization will use the new database.

Note

All views that already have materialization configured will not be affected by the change. All of the views that had persistent materialization defined, and now do not, when a persistent materialization is re-enabled, will retain the configured physical object names. To change it, manually edit the database part of the physical object name.