%3CLINGO-SUB%20id%3D%22lingo-sub-1643510%22%20slang%3D%22en-US%22%3ELab%3A%20Serverless%20Synapse%20-%20From%20Spark%20to%20SQL%20On%20Demand%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1643510%22%20slang%3D%22en-US%22%3E%3CP%3EDocumentation%20says%3A%3C%2FP%3E%0A%3CP%3E%22SQL%20on-demand%20is%20serverless%2C%20hence%20there%20is%20no%20infrastructure%20to%20setup%20or%20clusters%20to%20maintain.%20A%20default%20endpoint%20for%20this%20service%20is%20provided%20within%20every%20Azure%20Synapse%20workspace%2C%20so%20you%20can%20start%20querying%20data%20as%20soon%20as%20the%20workspace%20is%20created.%20There%20is%20no%20charge%20for%20resources%20reserved%2C%20you%20are%20only%20being%20charged%20for%20the%20data%20scanned%20by%20queries%20you%20run%2C%20hence%20this%20model%20is%20a%20true%20pay-per-use%20model.%3C%2FP%3E%0A%3CP%3EIf%20you%20use%20Apache%20Spark%20for%20Azure%20Synapse%20in%20your%20data%20pipeline%2C%20for%20data%20preparation%2C%20cleansing%20or%20enrichment%2C%20you%20can%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsynapse-analytics%2Fsql%2Fdevelop-storage-files-spark-tables%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%20data-linktype%3D%22relative-path%22%3Equery%20external%20Spark%20tables%3C%2FA%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eyou've%20created%20in%20the%20process%2C%20directly%20from%20SQL%20on-demand.%22%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsynapse-analytics%2Fsql%2Fon-demand-workspace-overview%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsynapse-analytics%2Fsql%2Fon-demand-workspace-overview%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAbout%20Spark%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsynapse-analytics%2Fspark%2Fapache-spark-overview%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsynapse-analytics%2Fspark%2Fapache-spark-overview%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%22Apache%20Spark%20provides%20primitives%20for%20in-memory%20cluster%20computing.%20A%20Spark%20job%20can%20load%20and%20cache%20data%20into%20memory%20and%20query%20it%20repeatedly.%20In-memory%20computing%20is%20much%20faster%20than%20disk-based%20applications.%20Spark%20also%20integrates%20with%26nbsp%3Bmultiple%20programming%20languages%20to%20let%20you%20manipulate%20distributed%20data%20sets%20like%20local%20collections.%20There's%20no%20need%20to%20structure%20everything%20as%20map%20and%20reduce%20operations.%22%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EUsing%20Synapse%26nbsp%3B%20I%20have%20the%20intention%20to%20provide%20Lab%20loading%20data%20into%20Spark%20table%20and%20querying%20from%20SQL%20OD.%3C%2FP%3E%0A%3CP%3EThis%20was%20an%20option%20for%20a%20customer%20that%20wanted%20to%20build%20some%20reports%20querying%20from%20SQL%20OD.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20need%3A%3C%2FP%3E%0A%3CP%3E1)%20A%20Synapse%20Workspace%20(%20SQL%20OD%20will%20be%20there%20after%20the%20workspace%20creation)%3C%2FP%3E%0A%3CP%3E2)Add%20Spark%20to%20the%20workspace%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20do%20not%20need%3A%3C%2FP%3E%0A%3CP%3E1)%20SQL%20Pool.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EStep%20by%20Step%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3ELaunch%20Synapse%20Studio%20and%20create%20a%20new%20notebook.%20Add%20the%20following%20code%20(%20phyton)%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Launch%20Synapse.png%22%20style%3D%22width%3A%20887px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F216982i817F379B28CA52E2%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Launch%20Synapse.png%22%20alt%3D%22Launch%20Synapse.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%25%25pyspark%0A%0A%0A%0Afrom%26nbsp%3Bpyspark.sql.functions%26nbsp%3Bimport%26nbsp%3Bcol%2C%26nbsp%3Bwhen%0A%0Adf%26nbsp%3B%3D%26nbsp%3Bspark.read.load('abfss%3A%2F%2F%3CCONTAINER%3E%40%3CSTORAGEACCOUNT%3E.dfs.core.windows.net%2Ffolder%2Ffile.snappy.parquet'%2C%26nbsp%3Bformat%3D'parquet')%0A%0Adf.createOrReplaceTempView(%22pysparkdftemptable%22)%0A%0A%0A%0A%3C%2FSTORAGEACCOUNT%3E%3C%2FCONTAINER%3E%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAdd%20some%20magic%20by%20include%20another%20cell%20and%20running%20Scala%20on%20it%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%25%25spark%0A%0Aspark.sql(%22CREATE%26nbsp%3BDATABASE%26nbsp%3BIF%26nbsp%3BNOT%26nbsp%3BEXISTS%26nbsp%3BSeverlessDB%22)%0A%0Aval%26nbsp%3Bscala_df%26nbsp%3B%3D%26nbsp%3Bspark.sqlContext.sql%26nbsp%3B(%22select%26nbsp%3B*%26nbsp%3Bfrom%26nbsp%3Bpysparkdftemptable%22)%0A%0Ascala_df.write.mode(%22overwrite%22).saveAsTable(%22SeverlessDB.Parquet_file%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ERun.%3C%2FP%3E%0A%3CP%3EIf%20everything%20ran%20successfully%20you%20should%20be%20able%20to%20see%20your%20new%20database%20and%20table%20under%20the%20Data%20Option%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Data_serveless.png%22%20style%3D%22width%3A%20420px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F216983i7A38F4AEC51BD138%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Data_serveless.png%22%20alt%3D%22Data_serveless.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENow%20it%20is%20the%20easy%20part.%20Query%20the%20table%20(%20Right%20button%20-%26gt%3B%20New%20SQL%20Script%20-%26gt%3B%20Select)%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22query_sqlOD.png%22%20style%3D%22width%3A%20561px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F216984iB3C23DC4F594521F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22query_sqlOD.png%22%20alt%3D%22query_sqlOD.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3ESuper%20quick%20and%20easy.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20is%20it!%3C%2FP%3E%0A%3CP%3ELiliam%20C%20Leme%3C%2FP%3E%0A%3CP%3EUK%20Engineer%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-1643510%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20is%20a%20Lab%20with%20the%20purpose%20of%20load%20data%20on%20Spark%20table%20and%20querying%20from%20SQL%20OD.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1643510%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESynapse%20Spark%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESynapse%20SQL%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESynapse%20Studio%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Microsoft

Documentation says:

"SQL on-demand is serverless, hence there is no infrastructure to setup or clusters to maintain. A default endpoint for this service is provided within every Azure Synapse workspace, so you can start querying data as soon as the workspace is created. There is no charge for resources reserved, you are only being charged for the data scanned by queries you run, hence this model is a true pay-per-use model.

If you use Apache Spark for Azure Synapse in your data pipeline, for data preparation, cleansing or enrichment, you can query external Spark tables you've created in the process, directly from SQL on-demand." https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/on-demand-workspace-overview

 

About Spark: https://docs.microsoft.com/en-us/azure/synapse-analytics/spark/apache-spark-overview

"Apache Spark provides primitives for in-memory cluster computing. A Spark job can load and cache data into memory and query it repeatedly. In-memory computing is much faster than disk-based applications. Spark also integrates with multiple programming languages to let you manipulate distributed data sets like local collections. There's no need to structure everything as map and reduce operations."

 

Using Synapse  I have the intention to provide Lab loading data into Spark table and querying from SQL OD.

This was an option for a customer that wanted to build some reports querying from SQL OD.

 

You need:

1) A Synapse Workspace ( SQL OD will be there after the workspace creation)

2)Add Spark to the workspace

 

You do not need:

1) SQL Pool.

 

Step by Step

Launch Synapse Studio and create a new notebook. Add the following code ( phyton):

 

Launch Synapse.png

 

 

%%pyspark



from pyspark.sql.functions import col, when

df = spark.read.load('abfss://<container>@<storageAccount>.dfs.core.windows.net/folder/file.snappy.parquet', format='parquet')

df.createOrReplaceTempView("pysparkdftemptable")



 

 

Add some magic by include another cell and running Scala on it:

 

 

%%spark

spark.sql("CREATE DATABASE IF NOT EXISTS SeverlessDB")

val scala_df = spark.sqlContext.sql ("select * from pysparkdftemptable")

scala_df.write.mode("overwrite").saveAsTable("SeverlessDB.Parquet_file")

 

 

 

Run.

If everything ran successfully you should be able to see your new database and table under the Data Option:

 

Data_serveless.png

 

Now it is the easy part. Query the table ( Right button -> New SQL Script -> Select):

query_sqlOD.png

Super quick and easy.

 

 

That is it!

Liliam C Leme

UK Engineer