Tip of the day: How to export data from SQL

Published 07-02-2020 02:05 AM 1,435 Views

When ingesting data from SQL to Azure Data Explorer in CSV format, you may have experienced ingestion failure due to formatting problems.  

This happens when the SQL exports to CSV in an ADX-incompatible format. 

But don’t panic, there’s a very simple solution: export from SQL in PSV format for ingestion.  

 
 

SQLCSV.JPG

 

Export manually, or automate with the following process:  

  1. Define an Azure Data Factory pipeline that exports data from SQL to blob storage using PSV format. 
  1. Define an Event Grid on this storage container that ingests each new blob to Azure Data Explorer. 
%3CLINGO-SUB%20id%3D%22lingo-sub-1502600%22%20slang%3D%22en-US%22%3ETip%20of%20the%20day%3A%20How%20to%20export%20data%20from%20SQL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1502600%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20data-contrast%3D%22auto%22%3EWhen%20ingesting%20data%20from%20SQL%20to%20Azure%20Data%20Explorer%20in%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3E%3CSPAN%20data-contrast%3D%22auto%22%3ECSV%3C%2FSPAN%3E%3C%2FSTRONG%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%26nbsp%3Bformat%2C%20you%20may%20have%20experienced%20ingestion%20failure%20due%20to%20formatting%20problems.%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%20data-ccp-props%3D%22%7B%26quot%3B201341983%26quot%3B%3A0%2C%26quot%3B335559739%26quot%3B%3A160%2C%26quot%3B335559740%26quot%3B%3A259%7D%22%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20data-contrast%3D%22auto%22%3EThis%20happens%20when%20the%20SQL%20exports%20to%20CSV%20in%20an%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%26nbsp%3BADX-%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3Eincompatible%20format.%3C%2FSPAN%3E%3CSPAN%20data-ccp-props%3D%22%7B%26quot%3B201341983%26quot%3B%3A0%2C%26quot%3B335559739%26quot%3B%3A160%2C%26quot%3B335559740%26quot%3B%3A259%7D%22%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20data-contrast%3D%22auto%22%3EBut%20don%E2%80%99t%20panic%2C%20there%E2%80%99s%20a%20very%20s%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3Eimple%20solution%3A%20export%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%26nbsp%3Bfrom%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%26nbsp%3BSQL%20in%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3E%3CSPAN%20data-contrast%3D%22auto%22%3EPSV%3C%2FSPAN%3E%3C%2FSTRONG%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%26nbsp%3Bformat%20for%20ingestion.%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%20data-ccp-props%3D%22%7B%26quot%3B201341983%26quot%3B%3A0%2C%26quot%3B335559739%26quot%3B%3A160%2C%26quot%3B335559740%26quot%3B%3A259%7D%22%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CDIV%20id%3D%22tinyMceEditorTzvia%20Gitlin%20Troyna_0%22%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%20id%3D%22tinyMceEditorTzvia%20Gitlin%20Troyna_1%22%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22SQLCSV.JPG%22%20style%3D%22width%3A%20955px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F202814i2B7B0FB31A07D886%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22SQLCSV.JPG%22%20alt%3D%22SQLCSV.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20data-ccp-props%3D%22%7B%26quot%3B201341983%26quot%3B%3A0%2C%26quot%3B335559739%26quot%3B%3A160%2C%26quot%3B335559740%26quot%3B%3A259%7D%22%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20data-contrast%3D%22auto%22%3EExport%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%26nbsp%3Bmanually%2C%20or%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%26nbsp%3Bautomate%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3Ewith%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3Ethe%20following%20process%3A%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%20data-ccp-props%3D%22%7B%26quot%3B201341983%26quot%3B%3A0%2C%26quot%3B335559739%26quot%3B%3A160%2C%26quot%3B335559740%26quot%3B%3A259%7D%22%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3COL%3E%0A%3CLI%20data-leveltext%3D%22%251.%22%20data-font%3D%22%22%20data-listid%3D%221%22%20aria-setsize%3D%22-1%22%20data-aria-posinset%3D%221%22%20data-aria-level%3D%221%22%3E%3CSPAN%20data-contrast%3D%22auto%22%3EDefine%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%26nbsp%3Ban%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%26nbsp%3B%3C%2FSPAN%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fdata-factory%2Fconnector-azure-sql-database%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%3CSPAN%20data-contrast%3D%22none%22%3EA%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22none%22%3Ezure%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22none%22%3ED%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22none%22%3Eata%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22none%22%3EF%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22none%22%3Eactory%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22none%22%3E%26nbsp%3Bpipeline%3C%2FSPAN%3E%3C%2FA%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%26nbsp%3Bthat%20exports%20data%20from%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3ESQL%3C%2FSPAN%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%26nbsp%3Bto%20blob%20storage%26nbsp%3B%3C%2FSPAN%3E%3CI%3E%3CSPAN%20data-contrast%3D%22auto%22%3Eusing%20PSV%20format%3C%2FSPAN%3E%3C%2FI%3E%3CI%3E%3CSPAN%20data-contrast%3D%22auto%22%3E.%3C%2FSPAN%3E%3C%2FI%3E%3CSPAN%20data-ccp-props%3D%22%7B%26quot%3B134233279%26quot%3B%3Atrue%2C%26quot%3B201341983%26quot%3B%3A0%2C%26quot%3B335559739%26quot%3B%3A160%2C%26quot%3B335559740%26quot%3B%3A259%7D%22%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3COL%3E%0A%3CLI%20data-leveltext%3D%22%251.%22%20data-font%3D%22%22%20data-listid%3D%221%22%20aria-setsize%3D%22-1%22%20data-aria-posinset%3D%222%22%20data-aria-level%3D%221%22%3E%3CSPAN%20data-contrast%3D%22auto%22%3EDefine%20an%26nbsp%3B%3C%2FSPAN%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fdata-explorer%2Fingest-data-event-grid%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%3CSPAN%20data-contrast%3D%22none%22%3EEvent%20Grid%3C%2FSPAN%3E%3C%2FA%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%26nbsp%3Bon%20this%20storage%20container%20that%20ingests%20each%20new%20blob%20to%20Azure%20Data%20Explorer.%3C%2FSPAN%3E%3CSPAN%20data-ccp-props%3D%22%7B%26quot%3B134233279%26quot%3B%3Atrue%2C%26quot%3B201341983%26quot%3B%3A0%2C%26quot%3B335559739%26quot%3B%3A160%2C%26quot%3B335559740%26quot%3B%3A259%7D%22%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FLI%3E%0A%3C%2FOL%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-1502600%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20data-contrast%3D%22auto%22%3EWhen%20ingesting%20data%20from%20SQL%20to%20Azure%20Data%20Explorer%20in%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3E%3CSPAN%20data-contrast%3D%22auto%22%3ECSV%3C%2FSPAN%3E%3C%2FSTRONG%3E%3CSPAN%20data-contrast%3D%22auto%22%3E%26nbsp%3Bformat%2C%20you%20may%20have%20experienced%20ingestion%20failure%20due%20to%20formatting%20problems.%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%20data-ccp-props%3D%22%7B%26quot%3B201341983%26quot%3B%3A0%2C%26quot%3B335559739%26quot%3B%3A160%2C%26quot%3B335559740%26quot%3B%3A259%7D%22%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22SQLCSV.JPG%22%20style%3D%22width%3A%20955px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F202815i605FC08FE626172E%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22SQLCSV.JPG%22%20alt%3D%22SQLCSV.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1502600%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EADX%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EAzure%20Data%20Explorer%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Ekql%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EKusto%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Jul 02 2020 02:07 AM
Updated by: