Scala – Create Snowflake table programmatically

You are currently viewing Scala – Create Snowflake table programmatically
Photo by Dan LeFebvre on Unsplash

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.

Advertisements

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:

Happy Learning !!