Spark (PySpark) to extract from SQL Server
Use Apache Spark to connect to SQL Server, extract a table from SQL Server, and load the extracted rows into a Hive table:
- You will need to download JDBC driver for SQL Server
- Download the driver from: https://www.microsoft.com/en-us/download/details.aspx?id=11774
- Run code: /usr/bin/spark-submit --driver-class-path /home/centos/sqljdbc_4.0/enu/sqljdbc4.jar --master yarn-client my_script.py
import os
from pyspark import SparkConf,SparkContext
from pyspark.sql import HiveContext
conf = (SparkConf()
.setAppName("data_import")
.set("spark.dynamicAllocation.enabled","true")
.set("spark.shuffle.service.enabled","true"))
sc = SparkContext(conf = conf)
sqlctx = HiveContext(sc)
df = sqlctx.load(
source="jdbc",
url="jdbc:sqlserver://ec2-54-244-44-6.us-west-2.compute.amazonaws.com:1433;database=sales;user=my_username;password=my_password",
dbtable="orders")
## this is how to write to an ORC file
df.write.format("orc").save("/tmp/orc_query_output")
## this is how to write to a hive table
df.write.mode('overwrite').format('orc').saveAsTable("test_table")
