How can I make tables from serverless Azure Synapse views?

user_4774960 0
Viewed 517

I have a view in an on-demand (or "serverless") sql pool. My goal is to over data from the serverless views and materialize them as tables in the dedicated pool. Is this possible?

azure-synapse azure
2 Answers

There are a couple of options here:

  1. create a Synapse Pipeline with Copy activity. Use the serverless and the source and the dedicated sql pool as the sink. Make sure the 'Auto create table' option is set on the sink
  2. create a Synapse notebook that connects via jdbc to the serverless sql pool (it's just a sql endpoint right), and writes into dedicated sql pool via the synapsesql.write method. I did an example of that technique here.
user_15275040

As per the official Microsoft documentation:

Limitations

Views in Synapse SQL are only stored as metadata. Consequently, the following options aren't available:

  • There isn't a schema binding option
  • Base tables can't be updated through the view
  • Views can't be created over temporary tables
  • There's no support for the EXPAND / NOEXPAND hints
  • There are no indexed views in Synapse SQL

But, as an alternative, if your table is in dedicated SQL pool you can use CREATE TABLE AS SELECT (CTAS) that creates a new table based on the output of a SELECT statement. CTAS is the simplest and fastest way to create a copy of a table.

To know more, please refer CREATE TABLE AS SELECT (Azure Synapse Analytics).

user_159690410
Related