When I changed the target DDL in TD to remove the DOC_ID and making the table as NOPI / Multiset, I get an error
$FILE_READER[1]: TPT19003 LOB column data length error
The maximum characters in 1 line in above sample JSON data file is 1800 characters or less.
Regards,
Babar
If you want to read in only character data, you can only user record format "Text" or "Delimtied".
You cannot use "unformatted" as that is used for binary data where the schema defines EXACTLY how the data will be read in. And since your JSON data will be varying in length, JSON(32000) will not work in the schema because the file reader will be expecting to 32000 bytes.
Since you only have a single column, try "Delimited". The fact that you have no delimiter does not matter because the end-of-record will supercede the delimiter at the end of the record.
--SteveF
Another thing you must do is change your INSERT statement from:
'INSERT INTO D1_STG_WEB.FRESH_JSON ( :DATA_JSON );'
to:
'INSERT INTO D1_STG_WEB.FRESH_JSON VALUES ( :DATA_JSON );'
--SteveF
Has this issue been resolved?
I am still facign the same issue. Here is my code: -
DEFINE JOB LOAD_JSON_FROM_FILE
DESCRIPTION 'Load file using inserter operator'
(
/*****************************/
DEFINE SCHEMA FLIGHT_SCHEMA
DESCRIPTION 'FLIGHT RECORD SCHEMA'
(
FLIGHTPACK JSON(6400)
);
/*****************************/
DEFINE OPERATOR FLIGHT_READER()
DESCRIPTION 'TERADATA PARALLEL TRANSPORTER DATA CONNECTOR OPERATOR'
TYPE DATACONNECTOR PRODUCER
SCHEMA FLIGHT_SCHEMA
ATTRIBUTES
(
VARCHAR DirectoryPathyPath = 'C:\Research\TD 15 to Mongo',
VARCHAR FileName = 'MBA.json',
VARCHAR OpenMode = 'Read',
VARCHAR Format = 'Delimited'
);
/*****************************/
DEFINE OPERATOR FLIGHT_INSERTER
DESCRIPTION 'Teradata PT INSERTER OPERATOR'
TYPE INSERTER
SCHEMA *
ATTRIBUTES
(
VARCHAR TdpId = '192.168.111.129',
VARCHAR UserName = 'dbc',
VARCHAR UserPassword = 'dbc',
VARCHAR PrivateLogName = 'SQL_inserter_log'
);
/*****************************/
STEP export_to_file
(
APPLY ('INSERT INTO SAMPLES.FLIGHT_JSON VALUES (:FLIGHTPACK);')
TO OPERATOR (FLIGHT_INSERTER() )
SELECT FLIGHTPACK FROM OPERATOR (FLIGHT_READER());
);
); Now as you can see, here is the Table as defined: -
CREATE MULTISET TABLE SAMPLES.FLIGHT_JSON ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
DOC_ID BIGINT GENERATED BY DEFAULT AS IDENTITY
(START WITH 1
INCREMENT BY 1
MINVALUE -99999999999999999
MAXVALUE 99999999999999999
NO CYCLE),
FLIGHTPACK JSON(8388096) CHARACTER SET UNICODE)
PRIMARY INDEX ( DOC_ID );Now when I try to run the above, I get the error -
Hello there,
Figured this out guys. It was a simple syntax issue: - (see below)
STEP export_to_file
(
APPLY ('INSERT INTO SAMPLES.FLIGHT_JSON(FLIGHTPACK)VALUES (:FLIGHTPACK);')
TO OPERATOR (FLIGHT_INSERTER() )
SELECT FLIGHTPACK FROM OPERATOR (FLIGHT_READER());
);
);
Thanks.
Dears,
I have been unable to read JSON data from a flat file into Teradata v15. Here is my setup.
DEFINE JOB LOADING_JSON_DATA DESCRIPTION 'LOAD JSON STREAMS INTO TERADATA' ( DEFINE SCHEMA SCHEMA_myJSONfile DESCRIPTION 'JSON file SCHEMA' ( DATA_JSON JSON(32000) ); APPLY ('INSERT INTO D1_STG_WEB.FRESH_JSON ( :DATA_JSON );' ) TO OPERATOR ( $INSERTER() [@InserterInstances] ) SELECT * FROM OPERATOR ( $FILE_READER (SCHEMA_myJSONfile) [@ReaderInstances] ATTR ( DirectoryPath = @SourceDirectoryPath, FileName = 'myRecords.json' ) ); );myRecords.json file contents: (4 JSON documents each terminated by a new line character. Thus 1 JSON doc per line. First attribute of each JSON doc is "_id")
{"_id":{"$oid":"55356fc311e44d684430c78b"},"aperture": "4.5","camera":"NIKON D200","category":14,"collections_count":0," comments_count":0,"converted":27,"converted_bits":27," created_at":"2015-04-20T17:28:25-04:00","crop_version" :7,"current_page":1,"description":null," favorites_count":0,"feature":"fresh_today"," focal_length":"50","for_sale":false,"for_sale_date": null,"height":1213,"hi_res_uploaded":0," highest_rating":0,"highest_rating_date":null,"id": 105825555,"image_format":"jpeg","image_url":"https:// drscdn.500px.org/photo/105825555/m%3D2048_k%3D1_a%3D1/ f85af69de86bd7344a0e79f9b3715eac","images":[{"format": "jpeg","https_url":"https://drscdn.500px.org/photo/ 105825555/m%3D2048_k%3D1_a%3D1/ f85af69de86bd7344a0e79f9b3715eac","size":2048,"url":" https://drscdn.500px.org/photo/105825555/m%3D2048_k% 3D1_a%3D1/f85af69de86bd7344a0e79f9b3715eac"}],"iso":" 200","latitude":null,"lens":null," license_requests_enabled":true,"license_type":0," location":null,"longitude":null,"name":"Cornrows // B","nsfw":false,"positive_votes_count":0,"privacy": false,"rating":0,"record_ts":"2015-04-20T21:29:39+00: 00","request_to_buy_enabled":true,"sales_count":0," shutter_speed":"1/250","status":1,"store_download": false,"store_license":false,"store_print":false,"tags" :["blanco y negro","cornrows","d200","moda","strobist"],"taken_at" :"2015-04-18T20:52:11-04:00","times_viewed":0,"url":"/ photo/105825555/cornrows-b-by-andr%C3%A9s-nigro"," user":{"affection":550,"city":"santiago","country":" Chile","firstname":"AndRéS","fullname":"AndRéS NiGro","id":4396964,"lastname":"NiGro","store_on": true,"upgrade_status":0,"username":"nigr0"," userpic_https_url":"https://pacdn.500px.org/4396964/ 875d8e594c1d2a2157c3e613f54fe4a4ce7126b3/1.jpg?1"," userpic_url":"https://pacdn.500px.org/4396964/ 875d8e594c1d2a2157c3e613f54fe4a4ce7126b3/1.jpg?1"," usertype":0},"user_id":4396964,"votes_count":0,"width" :812} {"_id":{"$oid":"55356fc311e44d684430c78c"},"aperture": "2.0","camera":"T00J","category":8,"collections_count" :0,"comments_count":0,"converted":27,"converted_bits": 27,"created_at":"2015-04-20T17:28:15-04:00"," crop_version":5,"current_page":1,"description":"Jpeg" ,"favorites_count":0,"feature":"fresh_today"," focal_length":"2","for_sale":false,"for_sale_date": null,"height":3264,"hi_res_uploaded":0," highest_rating":20.7,"highest_rating_date":"2015-04- 20T17:29:10-04:00","id":105825543,"image_format":" jpeg","image_url":"https://drscdn.500px.org/photo/ 105825543/m%3D2048_k%3D1_a%3D1/ 287051c7f8b4118a276949b870b3dad0","images":[{"format": "jpeg","https_url":"https://drscdn.500px.org/photo/ 105825543/m%3D2048_k%3D1_a%3D1/ 287051c7f8b4118a276949b870b3dad0","size":2048,"url":" https://drscdn.500px.org/photo/105825543/m%3D2048_k% 3D1_a%3D1/287051c7f8b4118a276949b870b3dad0"}],"iso":" 50","latitude":null,"lens":null," license_requests_enabled":true,"license_type":0," location":null,"longitude":null,"name":"Cherry Blossom","nsfw":false,"positive_votes_count":1," privacy":false,"rating":20.7,"record_ts":"2015-04- 20T21:29:39+00:00","request_to_buy_enabled":true," sales_count":0,"shutter_speed":"1/500","status":1," store_download":false,"store_license":false," store_print":false,"tags":["Cherry","Flowers","Trees" ,"blossom","spring"],"taken_at":"2015-03-23T07:56:54- 04:00","times_viewed":1,"url":"/photo/105825543/ cherry-blossom-by-eleonora-miani","user":{"affection": 179,"city":"Venice","country":"","firstname":" Eleonora","fullname":"Eleonora Miani","id":10199673,"lastname":"Miani","store_on": true,"upgrade_status":0,"username":"mianieleonora"," userpic_https_url":"https://pacdn.500px.org/10199673/ 3d58ea6208749cde295b482c701e5d74b9d1ffbb/1.jpg?1"," userpic_url":"https://pacdn.500px.org/10199673/ 3d58ea6208749cde295b482c701e5d74b9d1ffbb/1.jpg?1"," usertype":0},"user_id":10199673,"votes_count":1," width":2448} {"_id":{"$oid":"55356fc311e44d684430c78d"},"aperture": "22.0","camera":"Canon EOS 650D","category":12,"collections_count":0," comments_count":0,"converted":27,"converted_bits":27," created_at":"2015-04-20T17:28:10-04:00","crop_version" :9,"current_page":1,"description":null," favorites_count":0,"feature":"fresh_today"," focal_length":"125","for_sale":false,"for_sale_date": null,"height":1000,"hi_res_uploaded":0," highest_rating":16.5,"highest_rating_date":"2015-04- 20T17:29:27-04:00","id":105825539,"image_format":" jpeg","image_url":"https://drscdn.500px.org/photo/ 105825539/m%3D2048_k%3D1_a%3D1/ 9bbdee310296ff64baa814465cfa0749","images":[{"format": "jpeg","https_url":"https://drscdn.500px.org/photo/ 105825539/m%3D2048_k%3D1_a%3D1/ 9bbdee310296ff64baa814465cfa0749","size":2048,"url":" https://drscdn.500px.org/photo/105825539/m%3D2048_k% 3D1_a%3D1/9bbdee310296ff64baa814465cfa0749"}],"iso":" 100","latitude":null,"lens":null," license_requests_enabled":false,"license_type":0," location":null,"longitude":null,"name":"Еще о весне","nsfw":false,"positive_votes_count":1,"privacy" :false,"rating":16.5,"record_ts":"2015-04-20T21:29: 39+00:00","request_to_buy_enabled":true,"sales_count": 0,"shutter_speed":"1/160","status":1,"store_download": false,"store_license":false,"store_print":false,"tags" :["вода","капли","лед"],"taken_at":"2015-04-20T20:32: 28-04:00","times_viewed":4,"url":"/photo/105825539/% D0%95%D1%89%D0%B5-%D0%BE-%D0%B2%D0%B5%D1%81%D0%BD%D0% B5-by-%D0%A1%D0%B5%D1%80%D0%B3%D0%B5%D0%B9-%D0%95%D1% 80%D0%B5%D0%BC%D0%B8%D0%BD","user":{"affection":888," city":"Saint Petersburg, Russia","country":"Россия","firstname":"Сергей"," fullname":"Сергей Еремин","id":11353945,"lastname":"Еремин","store_on": false,"upgrade_status":0,"username":"bw1zmgl05q"," userpic_https_url":"https://graph.facebook.com/ 100001805523713/picture?height=100\u0026width=100"," userpic_url":"https://graph.facebook.com/ 100001805523713/picture?height=100\u0026width=100"," usertype":0},"user_id":11353945,"votes_count":1," width":660} {"_id":{"$oid":"55356fc311e44d684430c78e"},"aperture": "3.5","camera":"NIKON D600","category":7,"collections_count":0," comments_count":0,"converted":27,"converted_bits":27," created_at":"2015-04-20T17:28:03-04:00","crop_version" :9,"current_page":1,"description":"Cold Time","favorites_count":0,"feature":"fresh_today"," focal_length":"105","for_sale":false,"for_sale_date": null,"height":2200,"hi_res_uploaded":0," highest_rating":0,"highest_rating_date":null,"id": 105825535,"image_format":"jpeg","image_url":"https:// drscdn.500px.org/photo/105825535/m%3D2048_k%3D1_a%3D1/ 2ac451b15496ae298eb780778ad3d595","images":[{"format": "jpeg","https_url":"https://drscdn.500px.org/photo/ 105825535/m%3D2048_k%3D1_a%3D1/ 2ac451b15496ae298eb780778ad3d595","size":2048,"url":" https://drscdn.500px.org/photo/105825535/m%3D2048_k% 3D1_a%3D1/2ac451b15496ae298eb780778ad3d595"}],"iso":" 100","latitude":null,"lens":null," license_requests_enabled":true,"license_type":0," location":null,"longitude":null,"name":"Cold Time","nsfw":false,"positive_votes_count":0,"privacy": false,"rating":0,"record_ts":"2015-04-20T21:29:39+00: 00","request_to_buy_enabled":true,"sales_count":0," shutter_speed":"1/200","status":1,"store_download": false,"store_license":false,"store_print":false,"tags" :["Nikon","chromik","dchro","germany","b\u0026w"," face","gesichter","man","mann","nikon d600","portrait","portrait-art","portraits","sw"," visage"],"taken_at":null,"times_viewed":0,"url":"/ photo/105825535/cold-time-by-dietmar-chromik","user": {"affection":10115,"city":"Moenchengladbach","country" :"Germany","firstname":"dietmar","fullname":"dietmar Chromik","id":538649,"lastname":"Chromik","store_on": true,"upgrade_status":0,"username":"chromik"," userpic_https_url":"https://pacdn.500px.org/538649/ a65ce68bccaddf8d9d98bb0381b183ee36014f16/1.jpg?3"," userpic_url":"https://pacdn.500px.org/538649/ a65ce68bccaddf8d9d98bb0381b183ee36014f16/1.jpg?3"," usertype":0},"user_id":538649,"votes_count":0,"width": 1760}Here is the table in Teradata v15 that I am trying to load this data into
CREATE SET TABLE D1_STG_WEB.FRESH_JSON ,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT, DEFAULT MERGEBLOCKRATIO ( DOC_ID BIGINT GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1 MINVALUE -99999999999999999 MAXVALUE 99999999999999999 NO CYCLE), DATA_JSON JSON(8388096) CHARACTER SET UNICODE COMPRESS USING TD_SYSFNLIB.JSON_COMPRESS DECOMPRESS USING TD_SYSFNLIB.JSON_DECOMPRESS ) UNIQUE PRIMARY INDEX ( DOC_ID );SourceFormat is set to "unformatted". Using Data Connector Producer operator to read the file and sending it to Selector operator for inserting into TD.
However, I get this error all the time.
Can anyone point out the issue here ? Any help / pointers would be much appriciated.