Introduction
Data Warehouses are the essential part for Enterprise level Data analysis. But developing or maintaining data warehouses is not an easy task. It requires a lot of sagacity and it is an emaciated task. Over the times, along with the development in technology sector, there is tool launched publicly in 2014 by Bob Mugila i.e. Snowflake. Snowflake solves all the problems of traditional warehouses and provides an ideal data warehouse solution. Let’s forte Snowflake together and candorly start with it.
Learning Objectives
- What is Snowflake?
- Snowflake Architecture
- Why to use Snowflake?
- Getting started with Snowflake
- Accessing Snowflake using Java Application
What is Snowflake?
It is an analytical data warehouse, which can be used as Software-as-a-service(SaaS). It provides a warehouse which is fast, easy to use, and far more flexible as compared to traditional warehouses. It is not developed on existing “big data” platforms such as Hadoop.
Snowflake Architecture
Snowflake is a hybrid model of traditional shared-disk database architectures and shared-nothing database architectures. As similar to traditional shared-disk architectures, Snowflake uses a central data repository for persisted data which is accessible from all compute nodes in the data warehouse. But similar to shared-nothing database architectures, Snowflake processes queries using massively parallel processing(MPP) compute clusters where each node in the cluster stores a portion of the entire data set locally. This approach offers the data management simplicity of a shared-disk architecture, but with the performance and scale-out benefits of a shared-nothing architecture.
Why to use Snowflake?
- There is no hardware for which you have to select, install or manage.
- It runs completely on Cloud infrastructure.
- It uses virtual compute instances for its compute requirements.
- There is no need to install, configure or manage any software.
- Web based UI, CLI, ODBC/JDBC drivers, Native Connector or third party connectors are available for connecting to Snowflake.
Getting started with Snowflake
For making a free trial account on Snowflake, visit trial.snowflake.com. After that register yourself.
After clicking on Continue, select the Enterprise edition and select the Cloud Provider as AWS.
In the region field select the which is nearest to your place.
In our case it’s Asia Pacific (Mumbai). Also fill the contact number and click on Get Started.
Check your mail ID, confirmation mail must be sent to your email address. Open the mail and click on Activate my Account. Create your username and password. We are done with the creation of the Snowflake account.
Accessing Snowflake using Java Application
There are 4 JDBC type drivers provided by Snowflake, that support core JDBC functionality. It is a must to install JDBC drivers in a 64-Bit environment and it requires Java version 1.8 (or higher).
The snowflake-jdbc is provided as a JAR file, available as an artifact in Maven for download directly into your Java-based projects. First verify the version of the driver you are currently using, before downloading or integrating the driver.
We’ll be using IntelliJ for building our project!
Step 1 : Let’s head over to the Snowflake console in order to download the required jar files for the project. Do make sure to select the right version for the project. In our case, we’ll be using version ‘3.9.2’. On the top right corner, we’ll find a help button. Click on that and select Download…
Step 2 : On the Downloads section, click JDBC Driver and click on the link Maven Repository.
Step 3 : From here let’s select the version of the driver, we’ll use 3.9.2
Step 4 : From here, select just the jar file, we’ll need in the project and download it.
Step 5 : We have downloaded the required JDBC drivers, now let’s head over to IntelliJ and create a new Maven project. Now to import the jdbc drivers we just downloaded, into our projects, Go on files and select Project Structure…
Step 6 : Now in the left panel select Modules and choose the Dependencies tab on the right. Now let’s add the external jar by clicking on the ‘+’ icon and select it from the downloaded folder.
Step 7 : We’re done importing our jar file. Now before moving forward let’s configure the pom.xml file of our project by adding the following highlighted elements. Remember to replace the version with the version of JDBC if we might be using some other version.
Step 8 : Since we’re done with the configuration part, let’s now move to the coding section. The code we’ll be using has been mentioned below. Replace your username and password (can specify read only service account details here) in the Connection class.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class SnowflakeDriverExample
{
public static void main(String[] args) throws Exception
{
System.out.println("Create JDBC connection");
Connection connection = getConnection();
System.out.println("Done creating JDBC connection\n");
Create statement
System.out.println("Create JDBC statement");
Statement statement = connection.createStatement();
System.out.println("Done creating JDBC statement\n");
Use database
System.out.println("Use Database");
statement.executeUpdate("use database test");
System.out.println("Done! Using Database Test.\n");
Create a table
System.out.println("Create demo table");
statement.executeUpdate("create or replace table demo(c1 string)");
System.out.println("Done creating demo table\n");
Insert a row
System.out.println("Insert 'hello world'");
statement.executeUpdate("insert into demo values ('hello world')");
System.out.println("Done inserting 'hello world'\n");
Query the data
System.out.println("Query demo");
ResultSet resultSet = statement.executeQuery("select * from demo");
System.out.println("Metadata:");
System.out.println("================================");
Fetch metadata
ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
System.out.println("Number of columns=" +
resultSetMetaData.getColumnCount());
for (int colIdx = 0; colIdx < resultSetMetaData.getColumnCount();
colIdx++) {
System.out.println("Column " + colIdx + ": type=" +
resultSetMetaData.getColumnTypeName(colIdx + 1));
}
Fetch data
System.out.println("\nData:");
System.out.println("================================");
int rowIdx = 0;
while (resultSet.next()) {
System.out.println("row " + rowIdx + ", column 0: " + resultSet.getString(1));
}
resultSet.close();
statement.close();
connection.close();
}
private static Connection getConnection()
throws SQLException
{
try
{
Class.forName("com.snowflake.client.jdbc.SnowflakeDriver");
}
catch (ClassNotFoundException ex)
{
System.err.println("Driver not found");
}
Build connection properties
String user = "<user>";
String password = "<password>";
Properties props = new Properties();
props.put("user", user);
props.put("password", password);
Connection con = DriverManager.getConnection("jdbc:snowflake://<account_name>.snowflakecomputing.com/", props);
return con; }
So in the code mentioned, we’re using a database named “Test”, creating a demo table and inserting a string ‘Hello World’ for demonstration purposes, fetch the metadata and finally display the data we just entered in our tables. Let’s see the output of the code above!
Conclusion
In nutshell, Snowflake can be accessed using Java applications. Integrating Snowflake using Java, developers can easily scale and process their code efficiently. Snowflake not only provides JDBC/ODBC drivers but also it provides various other options such as CLI, Web Based UI, Native Libraries, etc for connecting it. Hope above blog gives you good idea about integrating Java and Snowflake. Happy Coding!
