Java- Create Snowflake table programmatically

Naveen Nelamali

4 years ago

How to create Snowflake table programmatically in Java? To create a table you can use either Snowflake web console or use the below steps to execute a “create table” DDL statement using the Java language. In this article, I will explain how to create a Snowflake database and create a Snowflake table programmatically using Snowflake JDBC driver and Java language and also explain different connection string properties.

Pre-requisites

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

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>

Create a Snowflake Database from the Web console

In order to create a Database, First, logon to 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.

Create a Snowflake Table using Java language

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

Connection parameters

In order to connect to the Snowflake table from Java, 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")

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.


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

Making Connection to Snowflake database

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


 Connection connection = DriverManager.getConnection(jdbcUrl, properties)

Create Statement & execute Query to create a table

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


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

Make sure you close the connection & statement in a finally block of try-catch statement.

Complete Java Example which creates Snowflake table


import java.sql.DriverManager
import java.sql.Connection
import java.sql.Statement
public class CreateSnowflakeTable{
public static void main(String args[]){
  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
  String jdbcUrl = "jdbc:snowflake://oea82.us-east-1.snowflakecomputing.com/"

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

    System.out.println("Created JDBC statement")
  Statement statement = connection.createStatement()

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

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

This Java 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 using Scala language.

Reference:

Snowflake JDBC

Happy Learning !!