Seamless data transfer between different platforms is crucial for effective data management and analytics. One common scenario that we’ve helped many clients with involves migrating data from Hive tables in a Hadoop environment to the Snowflake Data Cloud.
In this blog, we’ll explore how to accomplish this task using the Snowflake-Spark connector.
Overview
By harnessing the power of the Snowflake-Spark connector, you’ll learn how to transfer your data efficiently while ensuring compatibility and reliability. Whether you’re a data engineer, analyst, or hobbyist, this blog will equip you with the knowledge and tools to confidently make this migration.
If you don’t have a Spark environment set up in your Cloudera environment, you can easily set up a Dataproc cluster on Google Cloud Platform (GCP) or an EMR cluster on AWS to do hands-on on your own.
Migration Architecture
Before we dive into the technical details, let’s understand the high-level architecture of our data migration diagram:
We have written a Pyspark Job and ran it in the Spark Environment to directly lift and shift the Hive tables from a Hadoop Cluster to Snowflake using the Spark-Snowflake and Snowflake-JDBC connectors
Basically, our Pyspark job will read the data from a particular Hive Table in the Hive database and create a data frame. Then, we will connect with our Snowflake account using credentials and load that data frame directly into a Snowflake table.
How to Load Hive Table Data into Snowflake Tables
Let’s now walk through the step-by-step process of migrating Hive tables to Snowflake using Spark.
Step 1: Spark Environment Setup for GCP/AWS Cloud (optional)
If you don’t have a Spark environment set up in your Cloudera environment, you can easily set up a Dataproc cluster on Google Cloud Platform (GCP) or EMR cluster on AWS using the following simple steps:
Spark Environment Setup on GCP Dataproc
a. Navigate to GCP Console:
Access the Google Cloud ConsoleÂ
b. Create a Dataproc Cluster:
Click on Navigation Menu > Dataproc > Clusters.
Create or select an already existing project.
Click Create Cluster.
Configure cluster settings, including enabling Hive and other required components.
Click Create to initiate the Dataproc cluster creation.
c. Access Dataproc Cluster:
Once the cluster is created, you can access it through the GCP Console or by connecting to the cluster using SSH.
Spark Environment Setup on EMR Cluster
a. You can easily set an EMR cluster on an AWS account using the following simple steps:
Sign in to AWS Management Console and navigate to the EMR service.
Click Create cluster and choose software (Hadoop, Hive, Spark, Sqoop) and configuration (instance types, node count).
Configure security (EC2 key pair).
Review settings and launch the cluster.
Access the cluster via SSH and submit jobs or queries.
Terminate the cluster when finished to avoid unnecessary costs.
b. Connect to EMR cluster using AWS SSH:
aws emr ssh --cluster-id --key-pair-file
Note: If you received this error – ssh: connect to host ec2-*******.ap-southeast-2.compute.amazonaws.com port 22: Connection timed out
Follow these steps to update the Security Group –
Go to the EC2 dashboard.
Select Security Groups.
Find ElasticMapReduce-master.
Edit inbound rules.
Add a rule for SSH (port 22) from your desired IP range (e.g., 0.0.0.0/0).
Save changes.
c. Run hive on the EMR shell to test your environment:
[hadoop@ip-172-*1-*1-***~]$ hive
Note: We will move forward with GCP Dataproc to explain our data migration process.
Step 2: Hive Table Creation and Data Load
Step 2.1: Define Hive Table
In Hive, create and populate the source table. Use the following HiveQL command as a reference:
CREATE TABLE .(
-- Define columns based on your schema
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '' STORED AS TEXTFILE;
The <separator> is the character that separates different fields in each row. For example, if your data is comma-separated (CSV), you can replace <separator> with ,.
Step 2.2: Upload a CSV file
Upload a CSV file from your local to the GCS bucket, which you want to load in your Hive Table.
Step 2.3: Populate Hive Table
Load data into the Hive table using appropriate methods, such as INSERT statements or external data loading.
Example - LOAD DATA INPATH '' INTO TABLE .;
Step 3: Spark Environment Setup
Step 3.1: Install PySpark
Usually, Pyspark is pre-installed on Dataproc clusters. If we just want to ensure pyspark is installed, we could run pyspark or pyspark --version. If it’s not installed, then you can use the following command:
pip3 install pyspark
Step 3.2: Spark-Snowflake Connector Dependencies Setup
As we are using Python + Spark, so we don’t need Snowflake Connector jar download/setup. When launching the PySpark script, include the required Snowflake dependencies using the --packages option. So, you can run the commands from Pyspark CLI.
pyspark --packages net.snowflake:snowflake-jdbc:,net.snowflake:spark-snowflake_:-spark_
Step 3: Snowflake Table Creation
Define Snowflake Table:
In Snowflake, create a SQL Worksheet and run the following commands to create a database and tables:
CREATE DATABASE ;
USE DATABASE ;
CREATE SCHEMA ;
CREATE TABLE ..(
-- Define columns based on your schema
);
Note: The snowflake table structure should be the same as the source Hive table.
Step 4: Lift and Shift Data Between Hive to Snowflake
Step 4.1: Open Spark shell
Run the below command to open a Pyspark shell.
pyspark --packages net.snowflake:snowflake-jdbc:,net.snowflake:spark-snowflake_:-spark_
Step 4.2: Initialize Spark Session for Hive and read Hive Table data
In your PySpark script, create a Spark session with Hive support to read data from the Hive table.
from pyspark.sql import SparkSession
spark = SparkSession.builder.appName("").enableHiveSupport().getOrCreate()
result = spark.sql("SELECT * FROM .")
result.show()
Step 4.3: Configure Snowflake Connection
Initialize Snowflake connection parameters in the script. Replace placeholders with actual Snowflake account details.
sfOptions = {
"sfURL": "",
"sfUser": "",
"sfPassword": "",
"sfRole": "",
"sfDatabase": "",
"sfSchema" : "",
"sfWarehouse": ""
}
Step 4.4: Write Data to Snowflake
Use the Snowflake connector to write data from the Hive table to the Snowflake table.
result.write.format("snowflake").options(**sfOptions).option("", "").mode("append").save()
Step 4.5: Run a select query on Snowflake Table to check the loaded data
SELECT * FROM ..;
If the above query runs successfully and returns loaded data, we can create a Spark job.
Step 5: Spark Job Creation & Execution
Step 5.1: Create a .py file with the below code snippet
from pyspark.sql import SparkSession
# Create a Spark session with Hive support
spark = SparkSession.builder.appName("HiveExample").enableHiveSupport().getOrCreate()
# Use Hive SQL to query the table
result = spark.sql("SELECT * FROM myDatabase.payment_table")
# Show the result
result.show()
#Initialize the parameters to access the Snowflake Account.
sfOptions = {
"sfURL": "https://************.snowflakecomputing.com",
"sfUser": "********",
"sfPassword" : "********",
"sfRole": "",
"sfDatabase": "",
"sfSchema": "",
"sfWarehouse": ""
}
result.write.format("snowflake").options(**sfOptions).option("dbtable", "payment_table").mode("append").save()
spark.stop()
Step 5.2: Execute Spark Job
To execute the .py file, run it using the Pyspark command along with the required packages and configurations:
spark-submit --packages net.snowflake:snowflake-jdbc:,net.snowflake:spark-snowflake_:-spark_ your_script_name.py
Replace your_script_name.py with the full path/actual name of your Python script containing the provided code.
Run a select query on Snowflake to check loaded table data:
Congratulations! You’ve now completed the initial setup for migrating your Hive Table data to Snowflake using Spark Job.
Key Takeaways
Here are some key takeaways from the blog’s process for migrating Hive tables to Snowflake using Spark:
Understanding the Architecture: Before diving into the technical details, it is important to understand the high-level architecture of the data migration process. This includes knowledge of the components involved and how they interact with each other.
Spark Environment Setup: Depending on the Environment (Cloudera, GCP, or AWS), a Spark environment setup is necessary. This ensures you have the necessary infrastructure to run Spark jobs for data migration.
Create a Snowflake Table: Define the target Snowflake table in the Snowflake database. The table structure should match the Hive source table structure to ensure compatibility.
Data Migration (Lift and Shift): Use Spark to read data from a Hive table and write it to a Snowflake table. Configure the Snowflake connection parameters in the script and use the Snowflake connector to perform the data transfer.
Spark Job Creation & Execution: Create a Python script containing Spark code to read from Hive and write to Snowflake. Run the script using the Pyspark command along with the required packages and configurations. We are using "spark_version": "3.3.2", "scala_version": "2.12.18", "java_version": "11.0.20.1" and "python_version": "3.10", so we can use a suitable Spark-snowflake and Snowflake-jdbc package (net.snowflake:snowflake-jdbc:3.13.5, net.snowflake:spark-snowflake_2.12:2.9.0-spark_3.1). You can visit Maven jdbc and Spark-Snowflake sites to get your supported package versions.
Testing and Validation: After executing the Spark job, run select queries against the Snowflake table to verify that the data was loaded successfully. This step ensures the integrity and accuracy of the migrated data.
Closing
By following these steps, you can seamlessly migrate data from Hive tables in Hadoop to Snowflake using Spark, enabling efficient data management and analytics in the cloud.
Is your organization going through a migration to Snowflake?
The experts at phData can help! Schedule a free Snowflake migration assessment today for answers, advice, and next steps to ensure your migration is a success.
More to explore
Data Engineering
Migrating PySpark to Snowflake with CoCo
Andy Bouts
·
November 17, 2026
Read article
Read article
Anthropic
phData Raises New Funding to Turn Enterprise Data Into Trusted AI Answers and Reliable Agents with Claude
phData
·
October 28, 2026
Read article
Read article
AWS
The road to the AWS Golden Jacket: 13 exams, one foundation
Ryan Leonard
·
October 26, 2026
Read article
Read article
Cookie Consent
We use cookies to improve your experience on our site. By using our site, you consent to cookies.
Cookie Preferences
Manage your cookie preferences below:
Essential cookies enable basic functions and are necessary for the proper function of the website.
NameDescriptionDurationCookie PreferencesThis cookie is used to store the user's cookie consent preferences.30 daysGoogle Tag Manager simplifies the management of marketing tags on your website without code changes.
Service URL: business.safety.google (opens in a new window)
NameDescriptionDurationcookiePreferencesRegisters cookie preferences of a user2 yearstdRegisters statistical data on users' behaviour on the website. Used for internal analytics by the website operator.sessionStatistics cookies collect information anonymously. This information helps us understand how visitors use our website.
Clarity is a web analytics service that tracks and reports website traffic.
Service URL: clarity.microsoft.com (opens in a new window)
NameDescriptionDurationANONCHKIndicates whether MUID is transferred to ANID, a cookie used for advertising. Clarity doesn't use ANID and so this is always set to 0.Session_clckPersists the Clarity User ID and preferences, unique to that site is attributed to the same user ID.12 months_clskConnects multiple page views by a user into a single Clarity session recording.12 monthsCLIDIdentifies the first-time Clarity saw this user on any site using Clarity.12 monthsGoogle Analytics is a powerful tool that tracks and analyzes website traffic for informed marketing decisions.
Service URL: business.safety.google (opens in a new window)
NameDescriptionDuration_gatUsed to monitor number of Google Analytics server requests when using Google Tag Manager1 minute_gidID used to identify users for 24 hours after last activity24 hours_ga_ID used to identify users2 years_galiUsed by Google Analytics to determine which links on a page are being clicked30 seconds_gaID used to identify users2 years__utmxUsed to determine whether a user is included in an A / B or Multivariate test.18 months__utmvContains custom information set by the web developer via the _setCustomVar method in Google Analytics. This cookie is updated every time new data is sent to the Google Analytics server.2 years after last activity__utmcUsed only with old Urchin versions of Google Analytics and not with GA.js. Was used to distinguish between new sessions and visits at the end of a session.End of session (browser)__utmzContains information about the traffic source or campaign that directed user to the website. The cookie is set when the GA.js javascript is loaded and updated when data is sent to the Google Anaytics server6 months after last activity__utmbUsed to distinguish new sessions and visits. This cookie is set when the GA.js javascript library is loaded and there is no existing __utmb cookie. The cookie is updated every time data is sent to the Google Analytics server.30 minutes after last activity__utmtUsed to monitor number of Google Analytics server requests10 minutes__utmaID used to identify users and sessions2 years after last activity_gac_Contains information related to marketing campaigns of the user. These are shared with Google AdWords / Google Ads when the Google Ads and Google Analytics accounts are linked together.90 daysMarketing cookies are used to follow visitors to websites. The intention is to show ads that are relevant and engaging to the individual user.
Facebook Pixel is a web analytics service that tracks and reports website traffic.
Service URL: www.facebook.com (opens in a new window)
NameDescriptionDuration_fbpUsed to track the user across multiple sessions.3 months_fbcUsed to track the last session visit.12 monthsfrUsed to track the user across multiple sessions.3 monthsGoogle Ads is an online advertising platform that enables businesses to create targeted ads displayed on Google search results and partner sites.
Service URL: business.safety.google (opens in a new window)
NameDescriptionDurationFPGCLAWGoogle uses cookies for advertising, including serving and rendering ads, personalizing ads (depending on your ad settings at g.co/adsettings), limiting the number of times an ad is shown to a user, muting ads you have chosen to stop seeing, and measuring the effectiveness of ads.90 Days__Secure-3PSIDCCTargeting cookie. Used to create a user profile and display relevant and personalised Google Ads to the user.2 years__Secure-3PSIDTSTargeting cookie. Used to create a user profile and display relevant and personalised Google Ads to the user.2 years__Secure-1PSIDTSTargeting cookie. Used to create a user profile and display relevant and personalised Google Ads to the user.2 years__Secure-1PAPISIDTargeting cookie. Used to create a user profile and display relevant and personalised Google Ads to the user.2 years__Secure-3PSIDTargeting cookie. Used to profile the interests of website visitors and display relevant and personalised Google ads.2 yearsConversionGoogle uses cookies for advertising, including serving and rendering ads, personalizing ads (depending on your ad settings at g.co/adsettings), limiting the number of times an ad is shown to a user, muting ads you have chosen to stop seeing, and measuring the effectiveness of ads.90 days_gcl_awGoogle uses cookies for advertising, including serving and rendering ads, personalizing ads (depending on your ad settings at g.co/adsettings), limiting the number of times an ad is shown to a user, muting ads you have chosen to stop seeing, and measuring the effectiveness of ads.90 Days_gcl_gsGoogle uses cookies for advertising, including serving and rendering ads, personalizing ads (depending on your ad settings at g.co/adsettings), limiting the number of times an ad is shown to a user, muting ads you have chosen to stop seeing, and measuring the effectiveness of ads.90 Days_gcl_gbGoogle uses cookies for advertising, including serving and rendering ads, personalizing ads (depending on your ad settings at g.co/adsettings), limiting the number of times an ad is shown to a user, muting ads you have chosen to stop seeing, and measuring the effectiveness of ads.90 Days_gac_gb_Google uses cookies for advertising, including serving and rendering ads, personalizing ads (depending on your ad settings at g.co/adsettings), limiting the number of times an ad is shown to a user, muting ads you have chosen to stop seeing, and measuring the effectiveness of ads.90 DaysFPGCLGBGoogle uses cookies for advertising, including serving and rendering ads, personalizing ads (depending on your ad settings at g.co/adsettings), limiting the number of times an ad is shown to a user, muting ads you have chosen to stop seeing, and measuring the effectiveness of ads.90 DaysADS_VISITOR_IDCookie required to use the options and on-site web services2 monthsAECAEC cookies ensure that requests within a browsing session are made by the user, and not by other sites. These cookies prevent malicious sites from acting on behalf of a user without that user's knowledge.6 months__Secure-3PAPISIDProfiles the interests of website visitors to serve relevant and personalised ads through retargeting.2 years__Secure-1PSIDCCTargeting cookie. Used to create a user profile and display relevant and personalised Google Ads to the user.2 years__Secure-1PSIDTargeting cookie. Used to create a user profile and display relevant and personalised Google Ads to the user.2 yearsHubSpot is an all-in-one marketing, sales, and customer service platform that streamlines business growth.
Service URL: legal.hubspot.com (opens in a new window)
NameDescriptionDurationmessagesUtkThis cookie is used to recognize visitors who chat with you via the chatflows tool. If the visitor leaves your site before they're added as a contact, they will have this cookie associated with their browser.13 months__hssrcWhenever HubSpot changes the session cookie, this cookie is also set to determine if the visitor has restarted their browser.session__hsscThis cookie keeps track of sessions.30 minuteshubspotapiThis cookie allows the user to access the app with the correct permissions.7 dayshubspotapi-prefsThis is used with the hubspotapi cookie to remember whether the user checked the 'remember me' box (controls the expiration of the main cookie's authentication).1 Year__hs_opt_outThis cookie is used by the opt-in privacy policy to remember not to ask the visitor to accept cookies again.13 monthshubspotapi-csrfThis is used for CSRF prevention - preventing third party websites from accessing your data. Expires after a year.1 year__hs_do_not_trackThis cookie can be set to prevent the tracking code from sending any information to HubSpot.13 months__hs_cookie_cat_prefThis cookie is used to record the categories a visitor consented to.6 months__hs_initial_opt_inThis cookie is used to prevent the banner from always displaying when visitors are browsing in strict mode.7 days__hs_gpc_banner_dismissThis cookie is used when the Global Privacy Control banner is dismissed.180 dayshs_ab_testThis cookie is used to consistently serve visitors the same version of an A/B test page they’ve seen before.session__hs_notify_banner_dismissThis cookie is used when the website uses a Notify consent banner type.180 dayshs-messages-is-openThis cookie is used to determine and save whether the chat widget is open for future visits.30 minuteshs-messages-hide-welcome-messageThis cookie is used to prevent the chat widget welcome message from appearing again for one day after it is dismissed.1 day__hsmemThis cookie is set when visitors log in to a HubSpot-hosted site.1 yearhs-membership-csrfThis cookie is used to ensure that content membership logins cannot be forged.sessionhs_langswitcher_choiceThis cookie is used to save the visitor's selected language choice when viewing pages in multiple languages.2 years__hstcThe main cookie for tracking visitors.13 monthshubspotutkThis cookie keeps track of a visitor's identity. It is passed to HubSpot on form submission and used when deduplicating contacts.13 monthsLinkedIn Insight is a web analytics service that tracks and reports website traffic.
Service URL: www.linkedin.com (opens in a new window)
NameDescriptionDurationlangUsed to remember language settings.Sessionli_fat_idUsed for member indirect identifier for conversion tracking.30 daysli_sugrUsed to make a probabilistic match of a user's identity.90 daysli_oatmlUsed for identifying LinkedIn Members for advertising and analytics.30 daysliapUsed for authentication of LinkedIn members.90 dayslidcUsed for routing and session management.24 hoursGPC Signal Honored