Is There an Action Available to Query a BigQuery Database?

Help
1

This topic was automatically generated from Slack. You can find the original thread here.

is there an action available to query a BigQuery database? I didn’t find it…

2

Based on the information provided, I will need to check the Pipedream documentation for any pre-built actions related to BigQuery. If such an action exists, it would be listed under the BigQuery app integrations on Pipedream. However, since I don’t have access to live data or the ability to browse external content, and you have not provided a specific PIPEDREAM_DOCS_URL related to BigQuery, I cannot directly confirm the existence of a BigQuery action. If you’re looking for a pre-built action to query a BigQuery database and couldn’t find it in the Pipedream UI, it’s possible that it might not be available at the moment. In that case, you can submit a request for this integration using the following form: Integration Request Form. Alternatively, you can use a custom code step within a Pipedream workflow to connect to BigQuery and perform queries using the BigQuery API. If you need assistance with writing custom code for this purpose, please let me know, and I can provide you with an example.

3

Right now BigQuery is configured under the Google Cloud app in Pipedream, but that is admittedly not very discoverable: https://pipedream.com/apps/google-cloud/triggers/bigquery-query-results cc

4

Thanks , I’m aware of that one, but that’s actually a trigger, not an action tho.

5

Oops, sorry about that! Good call — sounds like we should add an action for querying a GQ db. Would you mind submitting a request at pipdream.com/support?

6

And in the meantime let’s see if can help. I want to query a BigQuery database using the Google Cloud app. Can you help me do that in a code step?

7

Certainly! To query a BigQuery database using a code step in Pipedream, you can use the Google Cloud app credentials to authenticate your requests and the @pipedream/platform package to make HTTP requests. Below is an example of how you might set up a Node.js code step to query a BigQuery database: import { axios } from "@pipedream/platform"; export default defineComponent({ props: { google_cloud: { type: "app", app: "google_cloud", }, }, async run({ steps, $ }) { // Construct the BigQuery SQL query const query = `SELECT * FROM your_dataset.your_table LIMIT 10`; // Set up the BigQuery API URL const bigQueryApiUrl = `https://bigquery.googleapis.com/bigquery/v2/projects/${this.google_cloud.$auth.project_id}/queries`; // Make the API request to BigQuery const response = await axios($, { method: "POST", url: bigQueryApiUrl, headers: { Authorization: `Bearer ${this.google_cloud.$auth.oauth_access_token}`, "Content-Type": "application/json", }, data: { query: query, useLegacySql: false, // Set to true if you're using legacy SQL syntax }, }); // The response will contain the query results return response.data; }, }); Please replace your_dataset.your_table with the actual dataset and table you want to query. Also, ensure that your Google Cloud app has the necessary permissions and that the BigQuery API is enabled in your Google Cloud project. Remember to handle pagination if your query results exceed the page size, as the BigQuery API may return a token for the next page of results.

8

Thanks ! I just fired a request issue and will try Pi’s code right now.

9

Error - Request failed with status code 401
{“error”:{“code”:401,“message”:“Request had invalid authentication credentials. Expected OAuth 2 access token, login cookie or other valid authentication credential. See https://developers.google.com/identity/sign-in/web/devconsole-project.",“errors”:[{“message”:"Invalid Credentials”,“domain”:“global”,“reason”:“authError”,“location”:“Authorization”,“locationType”:“header”}],"

10

The error message you’re encountering indicates that there’s an issue with the authentication credentials being used in your request to the BigQuery API. This can happen for a few reasons, such as an expired access token, incorrect scopes, or the credentials not being passed correctly in the request headers. Here’s a step-by-step approach to troubleshoot and resolve the issue: 1. Check the OAuth Scopes: Ensure that the OAuth scopes you’ve set for the Google Cloud app include the necessary permissions to access BigQuery. 2. Refresh the Access Token: If the access token has expired, you may need to refresh it. Pipedream should handle this automatically, but if there’s an issue, you might need to reconnect the Google Cloud app in Pipedream. 3. Correctly Pass the Access Token: Make sure you’re passing the access token correctly in the Authorization header. Here’s an example of how you might do that in a Pipedream code step: import { axios } from "@pipedream/platform"; export default defineComponent({ props: { google_cloud: { type: "app", app: "google_cloud", }, }, async run({ steps, $ }) { const bigQueryApiUrl = `https://bigquery.googleapis.com/bigquery/v2/projects/${this.google_cloud.$auth.project_id}/queries`; try { const response = await axios($, { method: "POST", url: bigQueryApiUrl, headers: { Authorization: `Bearer ${this.google_cloud.$auth.oauth_access_token}`, "Content-Type": "application/json", }, data: { query: "SELECT ** FROM `your_dataset.your_table` LIMIT 10", useLegacySql: false, }, }); return response; } catch (error) { // Log the error to help with debugging console.error(error); throw error; } }, }); Replace your_dataset.your_table with your actual dataset and table name. 4. Check the Project ID: Verify that the project ID is correct and that the BigQuery API is enabled for that project in the Google Cloud Console. 5. *Review the Error Message: The error message may contain additional details that can help you identify the issue. If you’ve gone through these steps and are still encountering issues, it may be helpful

11

can you fix the authentication credentials using a method similar to this one?

module.exports = defineComponent({
props: {
google_cloud: {
type: “app”,
app: “google_cloud”,
}
},
async run({steps, $}) {
// Required workaround to get the @google-cloud/storage package
// working correctly on Pipedream
require(“@dylburger/umask”)()

**const** { Storage } = require('@google-cloud/storage')

**const** key = JSON.parse(**this**.google_cloud.$auth.key_json)
 
_// Creates a client from a Google service account key._
_// See https://cloud.google.com/nodejs/docs/reference/storage/1.6.x/global#ClientConfig_
**const** storage = **new** Storage({
  projectId: key.project_id,
  credentials: {
    client_email: key.client_email,
    private_key: key.private_key,
  }
})

_// Uncomment this section and rename for your bucket before running this code_
_// const bucketName = 'pipedream-test-bucket';_

**await** storage.createBucket(bucketName)
console.log(`Bucket ${bucketName} created.`)

},
})

12

To address the authentication issue with Google Cloud services within Pipedream, you can use the provided google_cloud app prop to handle authentication. The code you’ve shown is a good starting point. It uses the @google-cloud/storage package to interact with Google Cloud Storage, and it authenticates using a service account key from the google_cloud app prop. Here’s an updated version of your code that should work within Pipedream: import { axios } from "@pipedream/platform"; export default defineComponent({ props: { google_cloud: { type: "app", app: "google_cloud", }, }, async run({ steps, $ }) { // Required workaround to get the @google-cloud/storage package // working correctly on Pipedream require("@dylburger/umask")(); const { Storage } = require('@google-cloud/storage'); const key = JSON.parse(this.google_cloud.$auth.key_json); // Creates a client from a Google service account key. const storage = new Storage({ projectId: key.project_id, credentials: { client_email: key.client_email, private_key: key.private_key, }, }); // Uncomment this section and rename for your bucket before running this code // const bucketName = 'pipedream-test-bucket'; await storage.createBucket(bucketName); console.log(`Bucket ${bucketName} created.`); }, }); Make sure to uncomment and set the bucketName variable to the name of the bucket you want to create. This code assumes that the google_cloud app prop is correctly configured with a service account key that has the necessary permissions to create buckets in Google Cloud Storage. If you continue to experience issues with authentication, ensure that the service account key is valid and has the appropriate roles and permissions assigned in Google Cloud IAM. If you need further assistance, please visit Pipedream Support.

13

I need to query a bigquery database, not create buckets in Google Cloud Storage

14

To query a BigQuery database using Pipedream, you can use the @google-cloud/bigquery package. Here is an example of how you could structure your Pipedream component to perform a query: import { axios } from "@pipedream/platform"; export default defineComponent({ props: { google_cloud: { type: "app", app: "google_cloud", }, }, async run({ steps, $ }) { // Required workaround to get the @google-cloud/bigquery package // working correctly on Pipedream require("@dylburger/umask")(); const { BigQuery } = require('@google-cloud/bigquery'); const key = JSON.parse(this.google_cloud.$auth.key_json); // Creates a client from a Google service account key. const bigquery = new BigQuery({ projectId: key.project_id, credentials: { client_email: key.client_email, private_key: key.private_key, }, }); // The SQL query to run const sqlQuery = `SELECT * FROM \`your_dataset.your_table\``; const options = { query: sqlQuery, // Location must match that of the dataset(s) referenced in the query. location: 'US', }; // Runs the query const [rows] = await bigquery.query(options); console.log('Query Results:'); rows.forEach(row => console.log(row)); // Return the rows for use in later steps return rows; }, }); Please replace your_dataset.your_table with the actual dataset and table you wish to query. Also, ensure that the location property matches the location of your BigQuery dataset. This code assumes that you have already set up the google_cloud app within Pipedream and have the necessary permissions to execute queries on your BigQuery dataset. If you encounter any issues or need further assistance, please visit Pipedream Support.

15

ReferenceError
require is not defined

16

I apologize for the confusion earlier. In Pipedream workflows, you don’t need to use require to import packages. Instead, you can directly use import statements. If you’re trying to query a BigQuery database, you would typically use the @google-cloud/bigquery package, but since you’re encountering a ReferenceError with require, let’s use the built-in capabilities of Pipedream to make an HTTP request to the BigQuery API. Here’s how you can structure your Pipedream component to perform a BigQuery query using the axios function provided by Pipedream: import { axios } from "@pipedream/platform"; export default defineComponent({ props: { google_cloud: { type: "app", app: "google_cloud", }, }, async run({ steps, $ }) { const projectId = 'your-google-cloud-project-id'; // Replace with your Google Cloud Project ID const datasetId = 'your-bigquery-dataset-id'; // Replace with your BigQuery dataset ID const tableId = 'your-bigquery-table-id'; // Replace with your BigQuery table ID const sqlQuery = `SELECT * FROM \`${datasetId}.${tableId}\``; // Replace with your SQL query const accessToken = this.google_cloud.$auth.oauth_access_token; const url = `https://bigquery.googleapis.com/bigquery/v2/projects/${projectId}/queries`; const response = await axios($, { method: "POST", url, headers: { Authorization: `Bearer ${accessToken}`, "Content-Type": "application/json", }, data: { query: sqlQuery, useLegacySql: false, // Set to true if you're using legacy SQL syntax }, }); console.log('Query Results:', response.data); return response.data; }, }); Please replace the placeholders (your-google-cloud-project-id, your-bigquery-dataset-id, your-bigquery-table-id) with your actual Google Cloud Project ID, BigQuery dataset ID, and table ID. Also, replace the sqlQuery string with your actual SQL query. This code uses the BigQuery REST API to execute the query. It assumes that you have already set up the google_cloud app within Pipedream and have the necessary permissions to execute queries on your BigQuery dataset