How to Import Data into Snowflake via R? (Part 1: MS SQL Server, PostgreSQL, SQLite)

We discussed how to import Data into MS SQL Server via R last month, and are going to cover more platforms like PostgreSQL / SQLite by showing how to import data into Snowflake via R.

MS SQL Server into Snowflake

• Required package: RODBC
• Steps:

Step 0: Set up MS SQL server/ Snowflake in ODBC Data Source Admin.

Step 1: Define SQL Server connection and Snowflake connection.

Step 2: Define query and run it via sqlFetch in MS SQL Server

Step 3: Import the df into test_sql_to_sf_001 via sqlSave function.

Step 4: Close the connection.

library(RODBC)

# open a connection to MS SQL server
conn_sqlserver <- odbcDriverConnect('driver={SQL Server};server=server;database=database;trusted_connection=true')
df <- sqlFetch(conn_sqlserver , "sql_to_sf")

# write the data from the data frame into a SF table
conn_snowflake <- odbcConnect("ODBC_SNOWFLAKE", uid="uid", pwd="pwd")
sqlSave(conn_snowflake, df, tablename="test_sql_to_sf_001", rownames=FALSE)

#close the connection to SQL Server / SF
odbcClose(conn_sqlserver)
odbcClose(conn_snowflake)

PostgreSQL Server into Snowflake

Download PostgreSQL

PostgreSQL ODBC Driver

• Required package: RODBC
• Steps:

Step 0: Set up PostgreSQL/ Snowflake in ODBC Data Source Admin.

PostgreSQL ODBC Setup

Step 1: Define PostgreSQL connection and Snowflake connection.

Step 2: Define query and run it via sqlFetch in PostgreSQL

Step 3: Import the df into test_postgresql_to_sf_001 via sqlSave function.

Step 4: Close the connection.

library(RODBC)

# open a connection to PostgreSQL
conn_postgresql <- odbcConnect('PostgreSQL')
df <- sqlFetch(conn_postgresql , "sql_to_sf")

# write the data from the data frame into a SF table
conn_snowflake <- odbcConnect("ODBC_SNOWFLAKE", uid="uid", pwd="pwd")
sqlSave(conn_snowflake, df, tablename="test_postgresql_to_sf_001", rownames=FALSE)

#close the connection to SQL Server
odbcClose(conn_postgresql)
odbcClose(conn_snowflake)

SQLite Server into Snowflake

SQLite Manager (Google Chrome) /(Firefox)

DB Browser for SQLite

SQLite ODBC Driver

• Required package: RODBC
• Steps:

Step 0: Set up PostgreSQL/ Snowflake in ODBC Data Source Admin.

Step 1: Define SQLite connection and Snowflake connection.

Step 2: Define query and run it via DBI::dbGetQuery in SQLite

Step 3: Import the df into test_sqlite_to_sf_001 via sqlSave function.

Step 4: Close the connection.

library(DBI)
library(dplyr)
library(dbplyr)
library(odbc)
library(RODBC)

# open a connection to SQLite
conn_sqlite <- DBI::dbConnect(odbc::odbc(), "ODBC_SQLite")
df_sqlite <- DBI::dbGetQuery(conn_sqlite, "select * from sql_to_sf")

# write the data from the data frame into a SF table
conn_snowflake <- odbcConnect("ODBC_SNOWFLAKE", uid="uid", pwd="pwd")
sqlSave(conn_snowflake, df_sqlite , tablename="test_sqlite_to_sf_001", rownames=FALSE)

#close the connection 
odbcClose(conn_sqlite)
odbcClose(conn_snowflake)

Here are some other our articles for data import & flow across the platforms. Take a look if you are interested in these topics.

Python

• How to Import Data into Teradata via Python?
• How to Import Data into Snowflake via Python?

JavaScript

• How to Import Data into MS SQL Server via JavaScript Node JS (Part 1: csv & Excel)?
• How to Import Data into Snowflake via JavaScript Node JS (Part 1: csv & Excel)?
• How to Import Notion data into MS SQL Server via JavaScript NodeJS?

Real Projects

• How to do Newegg Website Crawling for laptop price and spec via Scrapy and importing the data into SQLite ?
• How to do B&H Website Crawling for laptop price and spec via Scrapy and importing the data into SQLite?
• How to Scrape Fire Bureau website via regular expression and importing the data into SQLite?

Thank you :)