Hi Snaplogic gurus,
Quick question regarding PostgreSQL Bulk Load snap when input is binary (output from file reader which is a csv) does the psql command also needs to be setup in the nodes to work? something like the SQL bulk load wherein it utilizes the bcp.exe
Thanks,
EmEm
This didn’t worked for me
Failure: Map type does not have a method named: map, found in: …]: date[1]} ))). Perhaps you meant: mapKeys, mapValues, Resolution: Please check expression syntax and data types.
It didn’t because the first and second data source that you posted are completely different.
I updated my expression ( the only issue is that with the previous source, the data was on the root level, but on the new source, the data is within the “data” object ), please refer to the expression below:
{}.extend(...$data.entries().map(val => {[val[0]]: val[1].map(v => {}.extend(...v.entries().map(date => date[0].contains("_DATE") && date[1] == null ? {[date[0]]: "0000-00-00"} : {[date[0]]: date[1]} )))}))
Here is the result:
[
{
"data":{
"TIMEDEPENDENTDATA":[
{
"ASSET":"000050",
"SUBNUMBER":"0000",
"FROM_DATE":"1900-01-01",
"TO_DATE":"9999-12-31",
"W_DATE":"0000-00-00",
"X_DATE":"0000-00-00"
}
],
"SELECTIONCRITERIA":[
{
"PARAMETER":"GENERALDATA",
"FIELD":"ASSET",
"SIGN":"I",
"OPTION":"E",
"LOW":"00005000",
"HIGH":""
}
],
"REALESTATE":[
],
"POSTINGINFORMATION":[
{
"ASSET":"0000502",
"SUBNUMBER":"0000",
"C_DATE":"2006-08-31",
"DEACT_DATE":"0000-00-00",
"PLRET_DATE":"0000-00-00",
"PO_DATE":"0000-00-00",
"CAP_KEY":""
}
],
"NETWORTHVAL":[
],
"INVENTORY":[
{
"ASSET":"000050",
"SUBNUMBER":"0000",
"V_DATE":"0000-00-00",
"W_DATE":"0000-00-00"
}
],
"INSURANCE":[
]
}
}
]
Hi,
This still shows error:
Also, the $data is an object and inside that there are multiple arrays.
The PostgreSQL bulk load snap utilizes the COPY command available in PostgreSQL. COPY runs over JDBC like the regular PostgreSQL snaps, so no additional setup should be required for using the snap. The same is true for the Redshift bulk snaps also.
Thanks akidave, few more questions…
is there a certain format that the csv file needs to be followed? e.g quoted characters, row eliminator LF or CRLF
since I’m using a CSV, it will be ok to not specify any columns right (Header provided is checked) or it only applies to the streaming document?
How does the COPY command using the headers when format is CSV ?
I’m getting this error column record_id which is the 1st column and is a serial column, because my CSV doesn’t have the record_id and my table starting column is record_id then effective_timestamp
In order to allow insert for serial column in a COPY command it should be ommitted?
COPY table_name(column names) from myfile with csv
…myfile row 2
…myfile row 3
…
I’m doing this because there are about 500 tables that needs to be staged with a minimum of 200 columns… I don’t want to declare those columns in the snap ☹️
