{"id":95297,"date":"2023-01-16T17:00:06","date_gmt":"2023-01-16T17:00:06","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=95297"},"modified":"2023-01-16T00:44:50","modified_gmt":"2023-01-16T00:44:50","slug":"synapse-serverless-cetas-requires-dfs-to-work","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/synapse-serverless-cetas-requires-dfs-to-work\/","title":{"rendered":"Synapse Serverless: CETAS requires DFS to work"},"content":{"rendered":"

CETAS<\/strong> is the acronym for Create External Table As Select<\/strong>. This allow us to export the result of a select to an external table in different formats, such as PARQUET<\/strong> or CSV<\/strong>.<\/p>\n

We can also convert the format from one external table to another. The SELECT<\/strong> used on the CETAS<\/strong> can be an external table query. In this way we would be converting files in a data lake from one format to another. For example, we could convert CSV<\/strong> files to PARQUET<\/strong>.<\/p>\n

The following query is an example of CETAS:<\/p>\n

CREATE\u00a0EXTERNAL<\/span>\u00a0TABLE<\/span>\u00a0csvsampletables<\/span>
\nWITH<\/span>\u00a0(<\/span>
\n\u00a0 \u00a0 \u00a0 LOCATION<\/span>\u00a0=<\/span>\u00a0‘filescsv\/’<\/span>,<\/span>
\n\u00a0 \u00a0 \u00a0 DATA_SOURCE<\/span>\u00a0=<\/span>\u00a0SynapseSQLwriteable<\/span>,<\/span>
\n\u00a0 \u00a0 \u00a0 FILE_FORMAT<\/span>\u00a0=<\/span>\u00a0ParquetFF<\/span>
\n)<\/span>
\nAS<\/span><\/span><\/div>\n
select<\/span>\u00a0*<\/span>,<\/span>\u00a0tradedetail<\/span>.<\/span>filepath<\/b><\/span>(<\/span>1<\/span>)<\/span>\u00a0as<\/span>\u00a0datetime<\/span>,<\/span>
\n\u00a0 \u00a0 \u00a0 \u00a0 tradedetail<\/span>.<\/span>filepath<\/b><\/span>(<\/span>2<\/span>)<\/span>\u00a0as<\/span>\u00a0ticks<\/span>\u00a0from<\/span>
\nOPENROWSET<\/span>(<\/span>
\n\u00a0 \u00a0 \u00a0 \u00a0 BULK<\/span>\u00a0‘https:\/\/euwe01devqigsa01.blob.core.windows.net\/staging\/TradeDetail\/’<\/span>,<\/span>
\n\u00a0 \u00a0 \u00a0 \u00a0 FORMAT<\/span>=<\/span>‘CSV’<\/span>,<\/span>
\n\u00a0 \u00a0 \u00a0 \u00a0 HEADER_ROW<\/span>=<\/span>true<\/span>,<\/span>
\n\u00a0 \u00a0 \u00a0 \u00a0 parser_version<\/span>=<\/span>‘2.0’<\/span>
\n)<\/span>\u00a0AS<\/span>\u00a0[tradedetail]<\/span> <\/span><\/div>\n

\nThis query has two blob addresses: The SELECT<\/strong> query uses one address, while the create table uses a different address hidden in the Data Source called SynapseSQLWritable<\/em>.<\/p>\n

While making some CETAS<\/strong> tests, I discovered an interesting new behaviour. The following error message was displayed and it was very strange:<\/p>\n

Msg 16539, Level 16, State 1, Line 1<\/em><\/span>
\nOperation failed since the external data source ‘https:\/\/euwe01devqigsa01.blob.core.windows.net\/dennes\/filescsv\/’ has underlying storage account that is not in the list of Outbound Firewall Rules on the server. Please add this storage account to the list of Outbound Firewall Rules on your server and retry the operation.<\/em><\/span><\/p>\n

The Solution For CETAS<\/h2>\n

The error message doesn’t make much sense, because the Synapse Serverless Pool doesn’t have exposed Outbound Firewall Rules. In fact, the root cause and solution for this message is completely different from the message itself: CETAS<\/strong> is not supported using the BLOB<\/strong> protocol, only with the data lake protocol (DFS<\/strong>).<\/p>\n

Mind the query using the BLOB protocol: This IS NOT the problem. The query can use the blob protocol with absolutely no problem. The problem is hidden in the data source called SynapseSQLWritetable. We will need to drop this data source and create again using the DFS protocol:<\/p>\n

drop\u00a0external<\/span>\u00a0data<\/span>\u00a0source<\/span>\u00a0[SynapseSQLwriteable]<\/span> <\/p>\n

CREATE<\/span>\u00a0EXTERNAL<\/span>\u00a0DATA<\/span>\u00a0SOURCE<\/span>\u00a0[SynapseSQLwriteable]<\/span>\u00a0WITH<\/span>\u00a0(<\/span>
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0LOCATION<\/span>\u00a0=<\/span>\u00a0‘https:\/\/euwe01devqigsa01.dfs.core.windows.net\/dennes\/’<\/span>,<\/span>
\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0CREDENTIAL<\/span>\u00a0=<\/span>\u00a0[WorkspaceIdentity]<\/span>
\n)<\/span>;<\/span>GO<\/span> <\/span><\/div>\n

Once the data source is replaced, executing the query again it will work.<\/p>\n

More About Synapse Serverless<\/h2>\n
    \n
  1. How to query blob storage with SQL using Azure Synapse<\/a><\/li>\n
  2. How to query private blob storage with SQL and Azure Synapse<\/a><\/li>\n
  3. Performance of querying blob storage with SQL<\/a>\u00a0<\/li>\n<\/ol>\n

     <\/p>\n","protected":false},"excerpt":{"rendered":"

    CETAS is the acronym for Create External Table As Select. This allow us to export the result of a select to an external table in different formats, such as PARQUET or CSV. We can also convert the format from one external table to another. The SELECT used on the CETAS can be an external table……<\/p>\n","protected":false},"author":50808,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[124839,145436,158979,136298],"coauthors":[6810],"class_list":["post-95297","post","type-post","status-publish","format-standard","hentry","category-blogs","tag-azure-blob-storage","tag-azure-synapse","tag-cetas","tag-serverless"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/95297","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/50808"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=95297"}],"version-history":[{"count":3,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/95297\/revisions"}],"predecessor-version":[{"id":95300,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/95297\/revisions\/95300"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=95297"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=95297"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=95297"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=95297"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}