Skip to content

Lab020.md

Latest commit

 

History

History
57 lines (37 loc) · 1.89 KB

Lab020.md

File metadata and controls

57 lines (37 loc) · 1.89 KB
Raw

Lab 020: Shared Metadata

Each compute engine in Synapse (Spark and SQL Serverless) share much of their metadata. Databases and Parquet tables that are created in a Spark pool are automatically shared with SQL Serverless.

The default database in Spark and SQL Serverless are the same database. There is a slight delay, they are async.

Data security is handled at the storage level, so using a security principal is probably the right approach.

In a new pySpark notebook run this

spark.sql("CREATE DATABASE integration")

then wait a few seconds and from a SQL Serverless file run the following

SELECT * FROM sys.databases;

In Spark you have managed tables and external tables

  • managed tables are stored in teh warehouse directory in your workspace's defaultfs. Something like this: defaultfs/synapse/workspaces/x/warehouse. You should see an integration.db file there now.
  • external tables usually provide the LOCATION option.

Any parquet-based tables, both managed and external, are visible in SQL Serverless.

In your pySpark notebook run this

%%sql
CREATE TABLE integration.parquetexample(id int, name string, birthdate date) USING Parquet
INSERT INTO integration.parquetexample values (1,"dave","01/01/1980")

If you navigate to the datalake path above you should see the new managed table and some binary data representing that new row.

Now go back to SQL Serverless query window and run the following:

USE integration;
select * from integration.dbo.parquetexample;

External Table Example

Run this in pySpark:

CREATE TABLE mytestdb.myexternalparquettable
    USING Parquet
    //changeme
    LOCATION "abfss://<fs>@arcadialake.dfs.core.windows.net/synapse/workspaces/<synapse_ws>/warehouse/mytestdb.db/myparquettable/"

TODO: convert this in exploration-spark