yea, the arrays....we use jq to flatten and format all our JSON.... glad to see you got it!
dano On Thu, Sep 26, 2019 at 6:54 AM Nicolas Delsaux <nicolas.dels...@gmx.fr> wrote: > Oh well, i've understood my last error : incorrect flow files (with JSOn > arrays) were stuck in the queue. > > I removed them and ... to my delight, data seems to come in BigQuery ! > Le 26/09/2019 à 14:45, Nicolas Delsaux a écrit : > > I didn't knew that command ... i've edited some confidential values in the > result, but here it is > > > $ bq --project_id={{PROJECT_ID}} --format=prettyjson show -j > 9e790299-dc77-46f4-8978-476f284fe5b5 > { > "configuration": { > "jobType": "LOAD", > "load": { > "createDisposition": "CREATE_IF_NEEDED", > "destinationTable": { > "datasetId": "Consents", > "projectId": "{{PROJECT_ID}}", > "tableId": "{{TABLE_ID}}" > }, > "ignoreUnknownValues": false, > "maxBadRecords": 0, > "schema": { > "fields": [ > { > "fields": [ > { > "mode": "NULLABLE", > "name": "id", > "type": "STRING" > }, > { > "fields": [ > { > "mode": "NULLABLE", > "name": "id", > "type": "STRING" > }, > { > "mode": "NULLABLE", > "name": "type", > "type": "STRING" > }, > { > "mode": "NULLABLE", > "name": "businessUnit", > "type": "STRING" > } > ], > "mode": "NULLABLE", > "name": "identity", > "type": "RECORD" > }, > { > "mode": "NULLABLE", > "name": "finality", > "type": "STRING" > }, > { > "mode": "NULLABLE", > "name": "consentDate", > "type": "TIMESTAMP" > }, > { > "mode": "NULLABLE", > "name": "expiryDate", > "type": "TIMESTAMP" > }, > { > "mode": "NULLABLE", > "name": "expired", > "type": "BOOLEAN" > }, > { > "mode": "NULLABLE", > "name": "createdBy", > "type": "STRING" > }, > { > "mode": "NULLABLE", > "name": "createdDate", > "type": "TIMESTAMP" > }, > { > "fields": [ > { > "mode": "NULLABLE", > "name": "id", > "type": "STRING" > }, > { > "mode": "NULLABLE", > "name": "application", > "type": "STRING" > }, > { > "mode": "NULLABLE", > "name": "type", > "type": "STRING" > } > ], > "mode": "NULLABLE", > "name": "sender", > "type": "RECORD" > }, > { > "fields": [ > { > "mode": "NULLABLE", > "name": "id", > "type": "STRING" > }, > { > "mode": "NULLABLE", > "name": "type", > "type": "STRING" > } > ], > "mode": "NULLABLE", > "name": "relatedEvent", > "type": "RECORD" > } > ], > "mode": "NULLABLE", > "name": "ContractualConsent", > "type": "RECORD" > } > ] > }, > "sourceFormat": "NEWLINE_DELIMITED_JSON", > "writeDisposition": "WRITE_APPEND" > } > }, > "etag": "RqYxd6o2jzl6YiTARI5nxg==", > "id": "{{PROJECT_ID}}:EU.9e790299-dc77-46f4-8978-476f284fe5b5", > "jobReference": { > "jobId": "9e790299-dc77-46f4-8978-476f284fe5b5", > "location": "EU", > "projectId": "{{PROJECT_ID}}" > }, > "kind": "bigquery#job", > "selfLink": > "https://bigquery.googleapis.com/bigquery/v2/projects/{{PROJECT_ID}}/jobs/9e790299-dc77-46f4-8978-476f284fe5b5?location=EU" > <https://bigquery.googleapis.com/bigquery/v2/projects/%7B%7BPROJECT_ID%7D%7D/jobs/9e790299-dc77-46f4-8978-476f284fe5b5?location=EU> > , > "statistics": { > "creationTime": "1569491661818", > "endTime": "1569491662935", > "startTime": "1569491662366" > }, > "status": { > "errorResult": { > "message": "Error while reading data, error message: JSON table > encountered too many errors, giving up. Rows: 1; errors: 1. Please look > into the errors[] collection for more details.", > "reason": "invalid" > }, > "errors": [ > { > "message": "Error while reading data, error message: JSON table > encountered too many errors, giving up. Rows: 1; errors: 1. Please look > into the errors[] collection for more details.", > "reason": "invalid" > }, > { > "message": "Error while reading data, error message: JSON > processing encountered too many errors, giving up. Rows: 1; errors: 1; max > bad: 0; error percent: 0", > "reason": "invalid" > }, > { > "message": "Error while reading data, error message: JSON parsing > error in row starting at position 0: Start of array encountered without > start of object.", > "reason": "invalid" > } > ], > "state": "DONE" > }, > "user_email": > "rabbitmq-inges...@psh-analytics-automation.iam.gserviceaccount.com" > <rabbitmq-inges...@psh-analytics-automation.iam.gserviceaccount.com> > } > > Error message is interesting. > > If I look in data provenance at the data I'm expected to send to BigQuery, > I get > > > > [{"ContractualConsent":{"id":"5d847c5c92913700017692fc","identity":{"id":"511096128","type":"customer","businessUnit":"lmit"},"finality":"commercial_relationship","consentDate":"2019-06-04T15:39:32Z","expiryDate":"2024-06-04T15:39:32Z","expired":false,"createdBy":"DynamoCRM_DC","createdDate":"2019-09-20T07:14:36.576Z","sender":{"id":"511096128","application":"DYNAMO-CRM","type":"CUSTOMER"},"relatedEvent":{"id":"a72c44f1-de86-e911-a827-000d3a2aa91d","type":"customer_request"}}},{"ContractualConsent":{"id":"5d847c5c5fa9420001ebf04e","identity":{"id":"509582521","type":"customer","businessUnit":"lmit"},"finality":"commercial_relationship","consentDate":"2019-06-07T08:09:32Z","expiryDate":"2024-06-07T08:09:32Z","expired":false,"createdBy":"DynamoCRM_DC","createdDate":"2019-09-20T07:14:36.708Z","sender":{"id":"509582521","application":"DYNAMO-CRM","type":"CUSTOMER"},"relatedEvent":{"id":"6c335392-fb88-e911-a827-000d3a2aa91d","type":"customer_request"}}}] > > > Which is indeed an array, instead of an object. > > And maybe it is because my JsonRecordSetWriter has for "Output grouping" > the "Array" value selected ... > > > Well, strangely, even after having changed configuration of my > JsoNRecordSetwriter, values continue to be json arrays ... > > Anyway, I guess i'm on the right path ... (thanks a lot Pierre) > > > Le 26/09/2019 à 13:18, Pierre Villard a écrit : > > What if you run the below command in Cloud Shell: > bq --format=prettyjson show -j <job id> > > In your case (with your last email): > bq --format=prettyjson show -j 9e790299-dc77-46f4-8978-476f284fe5b5 > > Does it give you more details? > > Le jeu. 26 sept. 2019 à 12:13, Nicolas Delsaux <nicolas.dels...@gmx.fr> a > écrit : > >> Sorry for the late reply. >> >> As of today, the issue is still present. >> >> Nifi Web UI just shows the message "Error while reading data, error >> message: JSON table encountered too many errors, giving up. Rows: 1; >> errors: 1. Please look into the errors[] collection for more details." >> >> But the log is clearer : >> >> >> -------------------------------------------------- >> Standard FlowFile Attributes >> Key: 'entryDate' >> Value: 'Thu Sep 26 09:53:49 UTC 2019' >> Key: 'lineageStartDate' >> Value: 'Thu Sep 26 09:53:49 UTC 2019' >> Key: 'fileSize' >> Value: '999' >> FlowFile Attribute Map Content >> Key: 'avro.schema' >> Value: >> '{"type":"record","name":"nifiRecord","namespace":"org.apache.nifi","fields":[{"name":"ExplicitConsent","type":["null",{"type":"record","name":"ExplicitConsentType","fields":[{"name":"id","type":["null","string"]},{"name":"identity","type":["null",{"type":"record","name":"identityType","fields":[{"name":"id","type":["null","string"]},{"name":"type","type":["null","string"]},{"name":"businessUnit","type":["null","string"]}]}]},{"name":"finality","type":["null","string"]},{"name":"expired","type":["null","boolean"]},{"name":"createdBy","type":["null","string"]},{"name":"createdDate","type":["null","string"]},{"name":"sender","type":["null",{"type":"record","name":"senderType","fields":[{"name":"id","type":["null","string"]},{"name":"application","type":["null","string"]},{"name":"type","type":["null","string"]}]}]},{"name":"state","type":["null","string"]}]}]}]}' >> Key: 'bq.error.message' >> Value: 'Error while reading data, error message: JSON table encountered >> too many errors, giving up. Rows: 1; errors: 1. Please look into the >> errors[] collection for more details.' >> Key: 'bq.error.reason' >> Value: 'invalid' >> Key: 'bq.job.link' >> Value: >> 'https://www.googleapis.com/bigquery/v2/projects/{{PROJECT_ID}}/jobs/9e790299-dc77-46f4-8978-476f284fe5b5?location=EU' >> >> <https://www.googleapis.com/bigquery/v2/projects/psh-datacompliance/jobs/9e790299-dc77-46f4-8978-476f284fe5b5?location=EU'> >> Key: 'bq.job.stat.creation_time' >> Value: '1569491661818' >> Key: 'bq.job.stat.end_time' >> Value: '1569491662935' >> Key: 'bq.job.stat.start_time' >> Value: '1569491662366' >> Key: 'filename' >> Value: 'e6d604d7-b517-4a87-a398-e4a5df342ce6' >> Key: 'kafka.key' >> Value: '--' >> Key: 'kafka.partition' >> Value: '0' >> Key: 'kafka.topic' >> Value: 'dc.consent-life-cycle.kpi-from-dev-nifi-json' >> Key: 'merge.bin.age' >> Value: '1' >> Key: 'merge.count' >> Value: '3' >> Key: 'mime.type' >> Value: 'application/json' >> Key: 'path' >> Value: './' >> Key: 'record.count' >> Value: '3' >> Key: 'uuid' >> Value: 'e6d604d7-b517-4a87-a398-e4a5df342ce6' >> 2019-09-26 10:09:39,633 INFO [Timer-Driven Process Thread-4] >> o.a.n.processors.standard.LogAttribute >> LogAttribute[id=ce9c171f-0c8f-3cab-e0f2-16156faf15b8] logging for flow file >> StandardFlowFileRecord[uuid=e6d604d7-b517-4a87-a398-e4a5df342ce6,claim=StandardContentClaim >> [resourceClaim=StandardResourceClaim[id=1569490848560-6, container=default, >> section=6], offset=569098, >> length=999],offset=0,name=e6d604d7-b517-4a87-a398-e4a5df342ce6,size=999] >> >> >> I don't exactly understand why i would have to set an authentication, >> because I've set the service.json content into the GCP Credentials Provider >> I use for my PutBigQueryBatch processor ... >> >> Is there anything I'm missing ? or a simple way to make sure verything >> work as expected ? >> >> Thanks >> >> >> Le 24/09/2019 à 16:12, Pierre Villard a écrit : >> >> Hey Nicolas, >> >> Did you manage to solve your issue? Happy to help on this one. >> >> Thanks, >> Pierre >> >> Le ven. 20 sept. 2019 à 16:42, Nicolas Delsaux <nicolas.dels...@gmx.fr> >> a écrit : >> >>> Hello >>> >>> I'm using PutBigQueryBash and having weird auth issues. >>> >>> I have set the GCP Credentials Controller Service to use Service Account >>> JSON which I have copied from the value given in Google Cloud Console. >>> >>> But when I run my flow, I get the error message "Error while reading >>> data, error message: JSON table encountered too many errors, giving up. >>> Rows: 1; errors: 1. Please look into the errors[] collection for more >>> details." >>> >>> >>> What is stranger is that when I log all properties, there is a >>> bq.job.link which messages indicate "Request is missing required >>> authentication credential. Expected OAuth 2 access token, login cookie or >>> other valid authentication credential. See >>> https://developers.google.com/identity/sign-in/web/devconsole-project." >>> ... >>> >>> But nifi can access the bigquery workspace and dataset (I've checked >>> that by deleting the table schema that I have already written). >>> >>> So, is there something I'm doing wrong ? >>> >>> Thanks ! >>> >>