Error in parsing while load job tries to insert json to BigQuery table using java

1
votes

I am trying this ,

    Job job = new Job();
    JobConfiguration config = new JobConfiguration();
    JobConfigurationLoad loadConfig = new JobConfigurationLoad();
    config.setLoad(loadConfig);
    job.setConfiguration(config);

    // Set where you are importing from (i.e. the Google Cloud Storage paths).
    List<String> sources = new ArrayList<String>();
    sources.add(GCS_URL);
    loadConfig.setSourceUris(sources);

    boolean tableExists =checkTableExists(bigquery,projectId,datasetId,tableName);
    System.out.println("Whether table exists "+tableExists);

    TableReference tableRef = new TableReference();
    tableRef.setProjectId(projectId);
    tableRef.setDatasetId(datasetId);
    tableRef.setTableId(tableName);

    if(!tableExists)
    {
        System.out.println("Create table");
    Table table = new Table();
    table.setSchema(getSchemaForBqTable(tableName));   
    table.setTableReference(tableRef);
    bigquery.tables().insert(projectId,datasetId,table).execute();
    }


    loadConfig.setDestinationTable(tableRef);
    loadConfig.setSchema(getSchemaForBqTable(tableName));


    Insert insertJob =bigquery.jobs().insert(projectId, job);    
    insertJob.setProjectId(projectId);
    JobReference jobId  = insertJob.execute().getJobReference();


and also polling the status and error results ..
while (true) {
      pollJob = bigquery.jobs().get(projectId, jobId.getJobId()).execute();
      elapsedTime = System.currentTimeMillis() - startTime;
      System.out.format("Job status (%dms) %s: %s\n", elapsedTime,
          jobId.getJobId(), pollJob.getStatus().getState());

      if (pollJob.getStatus().getErrorResult() != null) {
          // The job ended with an error.
           System.out.format("Job %s ended with error %s", jobId.getJobId(),pollJob.getStatus().getErrorResult().getMessage(), projectId);
           throw new RuntimeException(String.format("Job %s ended with error %s", jobId.getJobId(), 
                   pollJob.getStatus().getErrorResult().getMessage()));       
       }     

      if (pollJob.getStatus().getState().equals("DONE")) {
        return pollJob;
      }

I am getting the below error

Job status (601ms) job_vJWfyum4tsmECoiBrygQ6PZn73c: PENDING Job status (1899ms) job_vJWfyum4tsmECoiBrygQ6PZn73c: PENDING Job status (3235ms) job_vJWfyum4tsmECoiBrygQ6PZn73c: PENDING Job status (5505ms) job_vJWfyum4tsmECoiBrygQ6PZn73c: PENDING Job status (7621ms) job_vJWfyum4tsmECoiBrygQ6PZn73c: PENDING Job status (8904ms) job_vJWfyum4tsmECoiBrygQ6PZn73c: PENDING Job status (10188ms) job_vJWfyum4tsmECoiBrygQ6PZn73c: PENDING Job status (11461ms) job_vJWfyum4tsmECoiBrygQ6PZn73c: PENDING Job status (13615ms) job_vJWfyum4tsmECoiBrygQ6PZn73c: PENDING Job status (14890ms) job_vJWfyum4tsmECoiBrygQ6PZn73c: PENDING Job status (16160ms) job_vJWfyum4tsmECoiBrygQ6PZn73c: PENDING Job status (17444ms) job_vJWfyum4tsmECoiBrygQ6PZn73c: PENDING Job status (19602ms) job_vJWfyum4tsmECoiBrygQ6PZn73c: PENDING Job status (20887ms) job_vJWfyum4tsmECoiBrygQ6PZn73c: PENDING Job status (22168ms) job_vJWfyum4tsmECoiBrygQ6PZn73c: PENDING Job status (24359ms) job_vJWfyum4tsmECoiBrygQ6PZn73c: RUNNING Exception in thread "main" Job status (25643ms) job_vJWfyum4tsmECoiBrygQ6PZn73c: DONE Job job_vJWfyum4tsmECoiBrygQ6PZn73c ended with error Data between close double quote (") and field separator: field starts with: java.lang.RuntimeException: Job job_vJWfyum4tsmECoiBrygQ6PZn73c ended with error Data between close double quote (") and field separator: field starts with: at com.disney.facebook.LoadJob.checkQueryResults(LoadJob.java:181) at com.disney.facebook.LoadJob.main(LoadJob.java:72)

--------------------------------------------------------------------------------------------------------------------------------------------------------

checked the job status using bq commands ...below the exception...

C:\Program Files (x86)\Google\Cloud SDK>bq show -j job_inR4kMvXQCcpcTujY7nEC-LZs BQ Job brilliobigquery-991:job_inR4kMvXQCcpcTujY7nEC-LZsBQ

Job Type State Start Time Duration Bytes Processed Bytes Bill ed Billing Tier

load FAILURE 04 Nov 12:18:32 0:00:01

Errors encountered during job execution. Data between close double quote (") and field separator: field starts with: Failure details: - File: 0 / Line:1 / Field:2: Data between close double quote (") and field separator: field starts with: - File: 0 / Line:1 / Field:3: Data between close double quote (") and field separator: field starts with: - File: 0 / Line:1 / Field:4: Data between close double quote (") and field separator: field starts with: - File: 0 / Line:1 / Field:5: Data between close double quote (") and field separator: field starts with:

File to upload will have content like this.

{"channel_skey":"124", "the_date":"1444953600", "total_subscribers":"782904", "total_subscribing":"21", "rec_insert_ts":"1444953600", "rec_update_ts":"1444953600"} {"channel_skey":"125", "the_date":"1444953600", "total_subscribers":"50359", "total_subscribing":"0", "rec_insert_ts":"1444953600", "rec_update_ts":"1444953600"}

Kindly help me to resolve this issue.

1
javagoogle-bigquery
Where you have the code that sets either JSON or CSV as input? – Pentium10

1 Answers