Is there a way to run SQL on demand in Synapse pipelines?

Andrey Vykhodtsev 11 Reputation points Microsoft Employee
2020-07-02T08:18:41.767+00:00

Hi folks, I can't find the way to run SQL on demand in the Synapse pipelines? There is a SQL Stored procedure, but it requires SQL pool.

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,652 questions
0 comments No comments
{count} vote

4 answers

Sort by: Most helpful
  1. HimanshuSinha-msft 19,381 Reputation points Microsoft Employee
    2020-09-24T05:22:04.577+00:00

    Hello anonymous user , @Greg Roberts ,

    At this time the SQL procedure does not support SQL on demand , but only SQL Pool . At the time SQL Pool is only supported by copy activity and few other activities .

    I would suggest you to submit the ask at https://feedback.azure.com/forums/307516-azure-synapse-analytics
    All of the feedback you share in these forums will be monitored and reviewed by the Microsoft engineering teams responsible for building Azure Synapse.

    Thanks
    Himanshu

    3 people found this answer helpful.

  2. PRADEEPCHEEKATLA-MSFT 84,936 Reputation points Microsoft Employee
    2020-07-06T07:39:16.467+00:00

    *anonymous user*,

    SQL on-demand is a query service over the data in your data lake. It enables you to access your data through the following functionalities:

    • A familiar T-SQL syntax to query data in place without the need to copy or load data into a specialized store.
    • Integrated connectivity via the T-SQL interface that offers a wide range of business intelligence and ad-hoc querying tools, including the most popular drivers.

    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. Use Private Link to bring your SQL on-demand endpoint into your managed workspace VNet.

    Reference: SQL on-demand (preview) in Azure Synapse Analytics

    Hope this helps. Do let us know if you any further queries.


    Do click on "Accept Answer" and Upvote on the post that helps you, this can be beneficial to other community members.


  3. Greg Roberts 16 Reputation points
    2020-09-24T08:06:33.243+00:00

    If you're just after reading data using the On-Demand pool, you can set it up as a Linked Service and create a generic dataset using it, which you can then use in Lookup or Copy activities.

    0 comments No comments

  4. Manish 1 Reputation point
    2020-10-21T05:58:50.18+00:00

    Please vote on this link for stored procedures support in Synapse On-Demand. The Product team has marked it as "Started"
    Link-https://feedback.azure.com/forums/307516-azure-synapse-analytics/suggestions/41454259-stored-procedure-support-for-on-demand-sql