Scala - Create Snowflake table programmatically

In this Snowflake tutorial, I will explain how to create a Snowflake database and create a Snowflake table programmatically using Snowflake JDBC driver and Scala language and also learn different connection string properties.

Pre-requisites

Snowflake data warehouse account
Basic understanding in Scala and IDE to run Scala programs

1. Snowflake JDBC driver dependency

You can download the Snowflake JDBC driver from Maven repository or use the following maven dependency. Use the latest version available from maven repository URL.

<dependency>
     <groupId>net.snowflake</groupId>
     <artifactId>snowflake-jdbc</artifactId>
     <version>3.12.0</version>
</dependency>

2. Create a Snowflake Database from the Web console

In order to create a Database, log on to the Snowflake web console, select the Databases from the top menu and select Create a New Database option and finally enter the database name on the form and select Finish button.

3. Create a Snowflake Table using Scala language

To create a table you can use either the Snowflake web console or use the below steps to execute a "create table" DDL statement using the Scala language.

3.1 Connection parameters

In order to connect to the Snowflake table from Scala, you need to provide the following minimum properties.

account : You account name, you can get this from URL for e.g “oea82”
user : Snowflake user name, typically your login user
password : user password
warehouse : Snowflake Dataware house name
db : Snowflake Database name
schema : Database schema where your table belongs
role : Snowflake user role


val properties = new java.util.Properties()
properties.put("user", "user")
properties.put("password", "#########")
properties.put("account", "oea82")
properties.put("warehouse", "mywh")
properties.put("db", "EMP")
properties.put("schema", "public")
properties.put("role","ACCOUNTADMIN")

3.2 JDBC connection string

In order to connect using JDBC driver, you need to provide the connection string, for Snowflake, it would be your snowflake account URL which contains account name, region name along with snowflakecomputing.com.


val jdbcUrl ="jdbc:snowflake://oea82.us-east-1.snowflakecomputing.com/"

3.3 Making Connection to Snowflake database

Using Java DriverManager.getConnection() to establish a connection with Snowflake, this method takes JDBC connection URL and above properties.


val connection = DriverManager.getConnection(jdbcUrl, properties)

3.4 Create Statement & execute Query to create a table

Like ANSI SQL, we can use either CREATE TABLE or CREATE OR REPLACE TABLE syntax.


val statement = connection.createStatement
statement.executeUpdate("create or replace table EMPLOYEE(name VARCHAR, department VARCHAR, salary number)")
statement.close

4. Complete Scala Example of Create Snowflake Table


import java.sql.DriverManager

object CreateSnowflakeTable extends App{

  val properties = new java.util.Properties()
  properties.put("user", "username")
  properties.put("password", "######1")
  properties.put("account", "oea82")
  properties.put("warehouse", "mywh")
  properties.put("db", "EMP")
  properties.put("schema", "public")
  properties.put("role","ACCOUNTADMIN")

  //JDBC connection string
  val jdbcUrl = "jdbc:snowflake://oea82.us-east-1.snowflakecomputing.com/"

  println("Created JDBC connection")
  val connection = DriverManager.getConnection(jdbcUrl, properties)
  println("Done creating JDBC connection")

  println("Created JDBC statement")
  val statement = connection.createStatement

  // create a table
  println("Creating table EMPLOYEE")
  statement.executeUpdate("create or replace table EMPLOYEE(name VARCHAR, department VARCHAR, salary number)")
  statement.close
  println("Done creating EMPLOYEE table")

  connection.close()
  println("End connection")
}

This Scala with Snowflake example is also available at GitHub project for reference.

Conclusion

In this tutorial, you have learned how to create a Snowflake database and executing a DDL statement, in our case executing SQL to create a Snowflake table.

Reference:

Snowflake JDBC

Happy Learning !!