%3CLINGO-SUB%20id%3D%22lingo-sub-1078043%22%20slang%3D%22en-US%22%3EUser-driven%20file%20selection%20for%20additional%20loading%20flexibility%20with%20the%20COPY%20statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1078043%22%20slang%3D%22en-US%22%3E%3CP%3EWe%20recently%20%3CA%20href%3D%22https%3A%2F%2Fazure.microsoft.com%2Fblog%2Fazure-sql-data-warehouse-is-now-azure-synapse-analytics%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3Eannounced%20the%20COPY%20statement%20for%20Azure%20Synapse%20Analytics%3C%2FA%3E%20(formerly%20Azure%20SQL%20Data%20Warehouse).%20Since%20then%2C%20customers%20have%20been%20able%20to%20take%20advantage%20of%20many%20flexible%20features%20within%20the%20COPY%20statement%20which%20enables%20a%20seamless%20loading%20experience.%20One%20feature%20which%20is%20becoming%20wildly%20popular%20is%20being%20able%20to%20specify%20wildcards%20and%20multi-file%20locations%20in%20a%20single%20COPY%20statement.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBefore%20the%20COPY%20statement%2C%20the%20loading%20experience%20was%20limited%20to%20only%20a%20single%20LOCATION%20parameter%20which%20pointed%20to%20either%20a%20file%20or%20folder.%20If%20a%20folder%20is%20specified%2C%20the%20load%20would%20retrieve%20all%20the%20files%20from%20the%20folder%20and%20all%20its%20subfolders.%20This%20all%20or%20nothing%20behavior%20posed%20manageability%20issues%20which%20was%20cumbersome%20for%20customers%20when%20exporting%20data%20to%20Azure%20and%20orchestrating%20ELT%20pipelines.%20Customers%20had%20to%20ensure%20specific%20source%20systems%20exported%20data%20to%20different%20folder%20structures%20(which%20may%20not%20always%20be%20feasible)%2C%20different%20LOCATION%20paths%20and%20External%20Table%20definitions%20were%20dynamically%20created%20during%20the%20load%20process%2C%20and%20the%20storage%20location%20path%20was%20cleaned%20up%20after%20each%20load%20and%20archived%20to%20separate%20locations.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWith%20the%20flexibility%20of%20wildcards%20in%20the%20COPY%20statement%2C%20customers%20can%20simply%20export%20to%20a%20single%20folder%20structure%20and%20target%20specific%20files%20by%20writing%20filtering%20logic%20using%20wildcards%20within%20their%20COPY%20statement.%20In%20addition%2C%20there%20are%20scenarios%20where%26nbsp%3Bdifferent%20source%20systems%20export%20data%20to%20different%20storage%20container%20paths%20where%20multi-file%20locations%20can%20simply%20be%20used%20for%20loading.%20Here%20are%20a%20few%20common%20usage%20examples%20of%20wildcards%20and%20multi-file%20locations%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELoad%20all%20parquet%20files%20ignoring%20other%20file%20formats%20that%20may%20be%20residing%20in%20the%20customers%20folder%20including%20its%20sub%20folders%3A%3C%2FP%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%3CPRE%20class%3D%22lia-code-sample%20language-javascript%22%3E%3CCODE%3ECOPY%20INTO%20customer_table%0AFROM%20'https%3A%2F%2Fmyaccount.blob.core.windows.net%2Fmyblobcontainer%2Fcustomers%2F*.parquet'%3B%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%3ELoad%20and%20back%20fill%20sales%20data%20during%20the%20month%20of%20December%202019%3A%3C%2FP%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%3CPRE%20class%3D%22lia-code-sample%20language-javascript%22%3E%3CCODE%3ECOPY%20INTO%20sales_table%0AFROM%20'https%3A%2F%2Fmyaccount.blob.core.windows.net%2Fmyblobcontainer%2Fsales%2F12*2019.parquet'%3B%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%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELoad%20files%20only%20from%20a%20known%20set%20of%20customers%3A%3C%2FP%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%3CPRE%20class%3D%22lia-code-sample%20language-javascript%22%3E%3CCODE%3ECOPY%20INTO%20top_customers_adhoc_V1%0AFROM%20'https%3A%2F%2Fmyaccount.blob.core.windows.net%2Fmyblobcontainer%2Fcustomers%2Fcustomer1'%2C%0A'https%3A%2F%2Fmyaccount.blob.core.windows.net%2Fmyblobcontainer%2Fcustomers%2Fcustomer2'%2C%0A'https%3A%2F%2Fmyaccount.blob.core.windows.net%2Fmyblobcontainer%2F%20customers%2Fcustomer3'%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%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELearn%20more%20about%20the%20COPY%20statement%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fsql%2Ft-sql%2Fstatements%2Fcopy-into-transact-sql%3Fview%3Dazure-sqldw-latest%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehere%3C%2FA%3E.%20Please%20send%20any%20feedback%20or%20issues%20to%20the%20following%20distribution%20list%3A%20sqldwcopypreview%40service.microsoft.com.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-1078043%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20COPY%20statement%20provides%20the%20most%20flexibility%20when%20loading%20into%20your%20database.%20Try%20the%20wildcard%20and%20multi-file%20list%20capability%20today!%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-center%22%20image-alt%3D%22clipboard_image_0.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F162912i1566722FB9107B11%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22clipboard_image_0.png%22%20alt%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1078043%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESynapse%20SQL%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Microsoft

We recently announced the COPY statement for Azure Synapse Analytics (formerly Azure SQL Data Warehouse). Since then, customers have been able to take advantage of many flexible features within the COPY statement which enables a seamless loading experience. One feature which is becoming wildly popular is being able to specify wildcards and multi-file locations in a single COPY statement.

 

Before the COPY statement, the loading experience was limited to only a single LOCATION parameter which pointed to either a file or folder. If a folder is specified, the load would retrieve all the files from the folder and all its subfolders. This all or nothing behavior posed manageability issues which was cumbersome for customers when exporting data to Azure and orchestrating ELT pipelines. Customers had to ensure specific source systems exported data to different folder structures (which may not always be feasible), different LOCATION paths and External Table definitions were dynamically created during the load process, and the storage location path was cleaned up after each load and archived to separate locations.

 

With the flexibility of wildcards in the COPY statement, customers can simply export to a single folder structure and target specific files by writing filtering logic using wildcards within their COPY statement. In addition, there are scenarios where different source systems export data to different storage container paths where multi-file locations can simply be used for loading. Here are a few common usage examples of wildcards and multi-file locations:

 

Load all parquet files ignoring other file formats that may be residing in the customers folder including its sub folders:

 

 

 

COPY INTO customer_table
FROM 'https://myaccount.blob.core.windows.net/myblobcontainer/customers/*.parquet';

 

 

 

Load and back fill sales data during the month of December 2019:

 

 

 

COPY INTO sales_table
FROM 'https://myaccount.blob.core.windows.net/myblobcontainer/sales/12*2019.parquet';

 

 

 

 

Load files only from a known set of customers:

 

 

 

COPY INTO top_customers_adhoc_V1
FROM 'https://myaccount.blob.core.windows.net/myblobcontainer/customers/customer1',
'https://myaccount.blob.core.windows.net/myblobcontainer/customers/customer2',
'https://myaccount.blob.core.windows.net/myblobcontainer/ customers/customer3'

 

 

 

 

Learn more about the COPY statement here. Please send any feedback or issues to the following distribution list: sqldwcopypreview@service.microsoft.com.