Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I inherited code that reads QVD data into a text string separated by a "|" symbol within a text file on a cloud sever. Then, an $(INCLUDE= .txt) file is written, after which SQL syntax is used to INSERT OVERWRITE the data into a DataBricks table store. This is the code (variable names etc. redacted):
//First the fields are read in from the QVD
[TABLE]:
LOAD
A,
B
C,
D,
E,
TABLE.Edited_By,
TABLE.Edited_At
FROM [lib://location/file.qvd]
(qvd);
//Then the QVD is transmuted into a text string, and then SQL-inserted into a DataBricks table store.
unqualify*;
NoConcatenate
table_qvd:
LOAD *
RESIDENT TABLE;
unqualify*;
NoConcatenate
table_qvd_str:
LOAD
CONCAT(
"A" & '|' &
"B" & '|' &
"C" & '|' &
"D" & '|' &
"E" & '|' &
TABLE.Edited_By" & '|' &
TABLE.Edited_At" &
'%') AS [SET DATA =] RESIDENT table_qvd;
STORE [SET DATA =] FROM table_qvd_str INTO "lib://location/table_qvd.txt" (TXT);
$(INCLUDE=[lib://location/table_qvd.txt]);
SQL INSERT OVERWRITE workspace.databricks_table VALUES ('$(DATA)')!EXECUTE_NON_SELECT_QUERY;
DROP TABLES table_qvd, table_qvd_str;
...generating this error at the point at which "Edited_At" and "Edited_By" are separated
Thank you for this information.
Thank you again for all your help. A REPLACE (FIELD, ';' , ',') as FIELD has solved this problem by replacing the problematic semicolon in the automatic audit field with a comma. All good now 🙂