Azure Databricks is the jointly developed data and AI service from Databricks and Microsoft for data analytics users. Azure Databricks is optimized for Azure data lakes and provides an interactive workspace to set up the environment and collaborate amongst the data scientist. Azure Databricks uses the SPARK engine to process the data. What is Azure Databricks?
SAS 9.4 users can access the Azure Databricks workspace and data tables using JDBC Driver. At present, there is no dedicated SAS/ACCESS Interface to the Databricks database. A new SAS/ACCESS engine for Databricks is schedule for the Aug-Sept release. In the meantime, users can use SAS/ACCESS to JDBC interface to access the Azure Databricks data table. Users can only read data from Azure Databricks using the JDBC Access engine. This post is about accessing the Azure Databricks data table from SAS 9.4 environment.
The following picture describes the SAS 9.4 environment access to the Azure Databricks database table.
Select any image to see a larger version.
Mobile users: To view the images, select the "Full" version at the bottom of the page.
Before you can access the data table from Azure Databricks, you need to have or create the Azure Databricks workspace. The Databricks workspace is the entry point for external applications to access the objects and data from Databricks. The Databricks workspace user credential is required to connect to the SPARK cluster from an external application.
The following screen describes the Azure Databricks Workspace and user credentials to access the SPARK cluster.
With Databricks workspace in place, you can create a SPARK cluster to process data ingested from Azure storage. The following screen describes the creation of the SPARK cluster under Azure Databricks Workspace
Azure Databricks SPARK cluster connection information is available at the cluster configuration tab.
The third-party application can access to Databricks table using the JDBC driver. The JDBC Driver is available at the following link.
With the SPARK cluster in place at Azure Databricks workspace, you can ingest data into the SPARK cluster from ADLS2 storage or Databricks File system files. The Databricks workspace has a Notebook editor to run Python code to interact with the SPARK cluster. The following Python statement ingests data from a JSON file to the SAPRK cluster and displays the data from the SPARK data frame.
#Read a sample data file (iot_devices.json) from Databricks DBFS location. df = spark.read.json("dbfs:/databricks-datasets/iot/iot_devices.json") #Create temporary view on Spark Data Frame "DF" df.createOrReplaceTempView('source') #Display top 10 ros from the source file. print('Displaying top 10 rows: ') display(spark.sql('SELECT * FROM source LIMIT 10'))
Before the data can be accessed from external applications, you need to write the SPARK data frame into a Databricks table. The following Python statement and screenshot describe the data written to Databricks table and available for external application.
#Write a parmanent table to share with other users and application. permanent_table_name = "iot_device" df.write.format("parquet").saveAsTable(permanent_table_name)
With Azure Databricks Workspace, SPARK Cluster, database table, and JDBC driver in place, you can use the following code to read data from the Azure Databricks table. The Azure Databricks Workspace token (key) is used as the password to authenticate to the environment.
options sastrace=',,,d' sastraceloc=saslog ; /* Note : variable value in quotes generate errors, So keep it without quotes. */ %let MYDBRICKS=adb-7060859955656306.6.azuredatabricks.net; %let MYPWD=dapiaa66843abadb51775a9dd7858d6980aa-2; %let MYHTTPPATH=sql/protocolv1/o/7060859955656306/0210-155120-shop163; %let MYUID=token; libname dbricks jdbc driverclass="com.simba.spark.jdbc.Driver" url="jdbc:spark://&MYDBRICKS:443/default;transportMode=http;ssl=1;httpPath=&MYHTTPPATH;AuthMech=3;UID=&MYUID;PWD=&MYPWD" classpath="/mnt/myazurevol/config/access-clients/JDBC/" schema="default" ; Proc SQL outobs=20;; select * from dbricks.iot_device ; run; quit;
Log extract :
Important Link: What is Azure Databricks ?
Find more articles from SAS Global Enablement and Learning here.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Data Literacy is for all, even absolute beginners. Jump on board with this free e-learning and boost your career prospects.