I am currently working with getting data out of the cloud for D365 F&O. Currently the data is exported to the Azure Datalake Gen2. All of the files are in a CSV format and there can be multiple in a directory.
I am looking to get the data via polybase with an on-prem SQL Server. Here are the steps I've followed so far.
https://www.sqlshack.com/sql-server-polybase-external-tables-with-azure-blob-storage/
I am new to Polybase and I am getting stuck on the external table piece.
This is my external file format. I can't use first_row as the syntax is not recognized and it shouldn't matter as my csv doesn't have any column headings.
CREATE EXTERNAL FILE FORMAT csvFile
WITH (
FORMAT_TYPE = DELIMITEDTEXT,
FORMAT_OPTIONS (
FIELD_TERMINATOR = ',',
STRING_DELIMITER = '"',
--FIRST_ROW = 2,
-- USE_TYPE_DEFAULT = FALSE,
ENCODING = 'UTF8' )
);
Here is my create external table, note I removed the location.
CREATE EXTERNAL TABLE dbo.vendinvoicejour (
[column1] varchar(1000),
[column2] varchar(1000),
[column3] varchar(1000),
[column4] varchar(1000),
[ORDERACCOUNT] varchar(1000),
[ATTORNEYDATE_RU] varchar(1000),
[ATTORNEYID_RU] varchar(1000),
[ATTORNEYISSUEDNAME_RU] varchar(1000),
[BANKLCIMPORTLINE] varchar(1000),
[CASHDISC] varchar(1000),
[CASHDISCCODE] varchar(1000),
[CASHDISCDATE] varchar(1000),
[CASHDISCPERCENT] varchar(1000),
[CONSIGNEEACCOUNT_RU] varchar(1000),
[CONSIGNORACCOUNT_RU] varchar(1000),
[CONSTARGET_JP] varchar(1000),
[CONTRACTNUM_SA] varchar(1000),
[CORRECT_RU] varchar(1000),
[CORRECTEDINVOICEDATE_RU] varchar(1000),
[CORRECTEDINVOICEID_RU] varchar(1000),
[CORRECTIONTYPE_RU] varchar(1000),
[COSTLEDGERVOUCHER] varchar(1000),
[COUNTRYREGIONID] varchar(1000),
[CURRENCYCODE] varchar(1000),
[DEFAULTDIMENSION] varchar(1000),
[DELIVERYADDRESS_LT] varchar(1000),
[DELIVERYDATE_ES] varchar(1000),
[DELIVERYNAME] varchar(1000),
[DELIVERYNAME_LT] varchar(1000),
[DELIVERYPOSTALADDRESS] varchar(1000),
[DESCRIPTION] varchar(1000),
[DLVADDRESS_LV] varchar(1000),
[DLVMODE] varchar(1000),
[DLVTERM] varchar(1000),
[DOCUMENTDATE] varchar(1000),
[DOCUMENTNUM] varchar(1000),
[DOCUMENTORIGIN] varchar(1000),
[DUEDATE] varchar(1000),
[ENDDISC] varchar(1000),
[ENDDISCMST] varchar(1000),
[ENTERPRISENUMBER] varchar(1000),
[EUSALESLIST] varchar(1000),
[EXCHRATE] varchar(1000),
[EXCHRATESECONDARY] varchar(1000),
[FACTUREDFULLY_RU] varchar(1000),
[FISCALDOCUMENTTYPE_BR] varchar(1000),
[FIXEDDUEDATE] varchar(1000),
[INCLTAX] varchar(1000),
[INTERCOMPANYCOMPANYID] varchar(1000),
[INTERCOMPANYLEDGERVOUCHER] varchar(1000),
[INTERCOMPANYPOSTED] varchar(1000),
[INTERCOMPANYSALESID] varchar(1000),
[INTERNALINVOICEID] varchar(1000),
[INTRASTATADDVALUE_LV] varchar(1000),
[INTRASTATDISPATCH] varchar(1000),
[INTRASTATFULFILLMENTDATE_HU] varchar(1000),
[INVENTBAILEERECEIPTREPORTID_RU] varchar(1000),
[INVENTPROFILETYPE_RU] varchar(1000),
[INVOICEACCOUNT] varchar(1000),
[INVOICEAMOUNT] varchar(1000),
[INVOICEAMOUNTMST] varchar(1000),
[INVOICEDATE] varchar(1000),
[INVOICEID] varchar(1000),
[INVOICEROUNDOFF] varchar(1000),
[INVOICETYPE] varchar(1000),
[ITEMBUYERGROUPID] varchar(1000),
[LANGUAGEID] varchar(1000),
[LEDGERVOUCHER] varchar(1000),
[LISTCODE] varchar(1000),
[LOGISTICSELECTRONICADDRESS] varchar(1000),
[NONREALREVENUE_RU] varchar(1000),
[NUMBERSEQUENCECODE_LT] varchar(1000),
[NUMBERSEQUENCEGROUP] varchar(1000),
[OFFSESSIONID_RU] varchar(1000),
[OPERATIONTYPE_MX] varchar(1000),
[PARMID] varchar(1000),
[PAYMDAYID] varchar(1000),
[PAYMENT] varchar(1000),
[PAYMENTSCHED] varchar(1000),
[PAYMID] varchar(1000),
[POSTINGPROFILE] varchar(1000),
[PREPAYMENT] varchar(1000),
[PROFORMA] varchar(1000),
[PURCHAGREEMENTHEADER_PSN] varchar(1000),
[PURCHASETYPE] varchar(1000),
[PURCHID] varchar(1000),
[PURCHRECEIPTDATE_W] varchar(1000),
[QTY] varchar(1000),
[REASONTABLEREF_BR] varchar(1000),
[REMITTANCEADDRESS] varchar(1000),
[RETURNITEMNUM] varchar(1000),
[REVERSECHARGEAMOUNT] varchar(1000),
[SALESBALANCE] varchar(1000),
[SALESPURCHOPERATIONTYPE_BR] varchar(1000),
[SERVICECODEONDLVADDRESS_BR] varchar(1000),
[SOURCEDOCUMENTHEADER] varchar(1000),
[SOURCEDOCUMENTLINE] varchar(1000),
[STATEINVOICEPRINTED_LV] varchar(1000),
[SUMLINEDISC] varchar(1000),
[SUMMARKUP] varchar(1000),
[SUMMARKUPMST] varchar(1000),
[SUMTAX] varchar(1000),
[TAXGROUP] varchar(1000),
[TAXINVOICEPURCHID] varchar(1000),
[TAXPRINTONINVOICE] varchar(1000),
[TAXROUNDOFF] varchar(1000),
[TAXSETOFFVOUCHER_IN] varchar(1000),
[TAXSPECIFYBYLINE] varchar(1000),
[TAXWITHHOLDAMOUNT_IN] varchar(1000),
[TRANSPORTATIONDOCUMENT] varchar(1000),
[TRIANGULATION] varchar(1000),
[UNITEDVATINVOICE_LT] varchar(1000),
[VATAMOUNT_IN] varchar(1000),
[VATNUM] varchar(1000),
[VATONPAYMENT_RU] varchar(1000),
[VENDCONSINVOICE_JP] varchar(1000),
[VENDFINALUSER_BR] varchar(1000),
[VENDGROUP] varchar(1000),
[VENDINVOICEDECLARATION_IS] varchar(1000),
[VENDINVOICEGROUP] varchar(1000),
[VENDORREQUESTEDWORKEREMAIL] varchar(1000),
[VENDPAYMENTGROUP] varchar(1000),
[VOLUME] varchar(1000),
[WEIGHT] varchar(1000),
[WHOISAUTHOR_LT] varchar(1000),
[CFDIUUID_MX] varchar(1000),
[INVOICESERIES_MX] varchar(1000),
[DATAAREAID] varchar(1000),
[PARTITION] varchar(1000),
[RECID] varchar(1000),
[RECVERSION] varchar(1000),
[MODIFIEDDATETIME] varchar(1000),
[CREATEDDATETIME] varchar(1000),
[CREATEDBY] varchar(1000),
[REPORTINGCURRENCYEXCHANGERATE] varchar(1000),
[CH_DBEREPORTABLE] varchar(1000),
[CH_SMALLBUSINESSINCENTIVE] varchar(1000),
[CH_DBEGOAL] varchar(1000),
[CH_PURCHAGREEMENTID] varchar(1000),
[CH_PURCHCARDSTATEMENT] varchar(1000),
[CH_HANDLINGCODE] varchar(1000),
[CE_DESCRIPTION] varchar(1000),
[PSNPURCHASINGCARDTYPE] varchar(1000),
[INTENTLETTERID_IT] varchar(1000),
[PLAFONDDATE_IT] varchar(1000),
[CH_VENDORSERVICEFROMDATE] varchar(1000),
[CH_VENDORSERVICETODATE] varchar(1000),
[CE_ORCAUDITORCERTREQUIRED] varchar(1000),
[CE_THENANDNOW] varchar(1000),
[CE_PAYBYWARRANT] varchar(1000),
[CE_ORCEXPLANATION] varchar(1000),
[IMPORTEDSALESTAX] varchar(1000),
[RECEIVEDDATE] varchar(1000),
[IMPORTEDAMOUNT] varchar(1000),
[CE_INVOICETYPEDESC] varchar(1000),
[CE_INVOICETYPE] varchar(1000),
[CE_REFDOCNUM] varchar(1000),
[TAXID] varchar(1000),
[PARTYTAXID] varchar(1000),
[STATE] nvarchar(2),
[VENDORVATDATE] varchar(1000),
[column5] varchar(1000))
WITH (
LOCATION = ''
DATA_SOURCE=AzureStorage2,
FILE_FORMAT= csvfile,
REJECT_TYPE = VALUE ,REJECT_VALUE = 1
);
I am getting this error when I try to do a select on vendinvoicejour.
