Great! Glad you got it working!
Le jeu. 26 sept. 2019 à 14:54, Nicolas Delsaux <[email protected]> a
écrit :
> 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 <[email protected]> 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 <[email protected]>
>> 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 !
>>>
>>
