Can't create BigQuery query jobs in nextjs project

[Pinqi]

Someone please help me , i'm desperate and I'm just a coop student working for a startup... being stucked for 2 days.

I'm writing my next.js application and i need to fetch some data from the BigQuery... I've tried using ADC, service account, but i just can't create a simple query job.... i can run the query job from command line using service account key, but my code won't work .  The console logs  would stop at 'Creating Query Job' ... I've read all the documents i can possibly find😩

Server side

export default async function fetchBigQuery(req: NextApiRequest, res: NextApiResponse) {
    console.log('fetchBigQuery invoked::', req.method)

    if (req.method !== 'GET') {
        res.status(405).end()  // Method not allowed
        return
    }

    const options = {
        keyFilename: '/path/to/service-acct.json',
        projectId: 'project-dev',
    }

    const bigquery = new BigQuery(options)

    const query = `SELECT * FROM alter-prime-dev.client_dmo_mar.out_dmo_mar_climate_hazards__assets LIMIT 10`

    console.log('did it get to here')

    try {
        console.log('Creating Query Job')
        const [job] = await bigquery.createQueryJob({ query:query })
        console.log('Job Created:', job.id)

        console.log('Getting Query Results')
        const [rows] = await job.getQueryResults()
        console.log('Query Results:', rows)


        if (rows.length === 0) {
            return res.status(200).json({ message: 'No data found' })
        }
        return res.status(200).json({ rows })  // Return the rows as a JSON response
    } catch (error) {
        return res.status(500).json({ error: 'BigQuery error' })
    }
}

  front end

'use client'

import { useEffect, useState } from 'react'

const NamesComponent = () => {
    const [names, setNames] = useState<string[]>([])

    const queryBigQuery = async()=>{
        console.log('About to fetch data')  // Debugging line
        try {
            const res = await fetch('/api/fetchBigQuery', {
                method: 'GET',
                headers: { 'Content-Type': 'application/json' },
            })

            if (res.ok) {
                const jsonData = await res.json()
                console.log('Fetched Data:', jsonData)  // Debugging line to view fetched data
                setNames(jsonData)  // Update the state with fetched data
            } else {
                console.error(`Error: ${res.status}`)
            }
        } catch (error) {
            console.error('Failed to fetch user data:', error)
        }
    }

    useEffect(() => {
        queryBigQuery()
    }, [])

    return (
        <div>
            <h1>Fetched Data:</h1>
            <pre>{JSON.stringify(names, null, 2)}</pre>
        </div>
    )
}

export default NamesComponent

[ms4446]

Based on the code you provided, it seems you're able to initiate the creation of a BigQuery query job using the BigQuery.createQueryJob() method. However, the process halts at the Job Created console log, indicating it doesn't proceed to the Getting Query Results section.

Possible reasons include:

• The query job might be taking longer than expected.
• The query job could have encountered an error.
• There might be an issue in the subsequent code, especially around the getQueryResults method.

To troubleshoot:

• Check Job Status: After creating the job, check its status to see if it's completed:
  
  

const metadata = await job.getMetadata();
if (metadata[0].status.state !== 'DONE') {
  console.log('Job is still running or has errors.');
  if (metadata[0].status.errors) {
    console.error('Job Errors:', metadata[0].status.errors);
  }
}

• Debugging: Add more console.log statements in your code to trace the execution flow and identify where it might be breaking.
• Optimize Your Query: If the query is taking too long, consider optimizing it or breaking it into smaller chunks.

[Pinqi]

it stucked before the the job is created actually, the last thing that console.log is Creating Query Job which is right before

const [job] = await bigquery.createQueryJob({ query:query })

[ms4446]

If the console log is stopping at "Creating Query Job", it indicates that the job creation process is encountering an issue before the job is even initiated. This could be due to several reasons:

• An issue with the service account key file's path or its content.
• A problem with the BigQuery API request.
• A potential issue with the query itself, although this seems less likely given that you can run it from the command line.

To troubleshoot:

1. Service Account Credentials: Verify the path to the service account key file and ensure its content is correct. Check the service account's permissions in the Google Cloud Console under IAM & Admin.

2. Query Verification: Since you can run the query from the command line, it's likely correct. However, double-checking in the BigQuery console can't hurt.

3. Client Library Version: Ensure you're using the latest version of the BigQuery client library. Outdated versions might have bugs or compatibility issues.

4. Different Environment: Consider running the code in a different environment, such as a cloud function or another local setup, to see if the issue persists.

5. Error Handling: Enhance the error handling in your code to capture and log any exceptions or errors that might occur during the job creation process.