BigQuery Plugin for Dify

Author: david-pivonka
Version: 0.0.1
Type: Tool Plugin

Description

The BigQuery plugin enables Dify applications to query and modify data in Google BigQuery directly. It provides secure, authenticated connections to BigQuery using Google Cloud service account credentials with separate tools for read and write operations.

Features

• ✅ Secure Authentication: Uses Google Cloud service account JSON for authentication
• ✅ Dual Tools: Separate tools for queries (SELECT) and mutations (INSERT/UPDATE/DELETE)
• ✅ Per-Query Row Limits: Control result size with configurable row limits at the query level
• ✅ SQL Validation: Automatic validation of SQL syntax before execution
• ✅ Cost Protection: Built-in query cost limits to prevent runaway billing
• ✅ Error Handling: Comprehensive error handling and logging
• ✅ Standardized Output: Returns data in a consistent format

Installation

Prerequisites

• A Google Cloud Platform account with BigQuery enabled
• A service account with appropriate BigQuery permissions
• Dify platform access

Required Permissions

Your Google Cloud service account must have at least these IAM roles:

• - To run queries
• - To read table data

Or specific dataset/table level permissions as needed.

Installing the Plugin

1. Download the file
2. In Dify, navigate to Plugins → Install Plugin
3. Upload the file
4. Configure the plugin credentials (see Configuration section)

The plugin provides two tools:

• BigQuery Query: For SELECT operations (read-only)
• BigQuery Mutation: For INSERT, UPDATE, DELETE operations (write operations)

Configuration

The plugin requires the following credentials:

service_account_json (Required)

The complete JSON content of your Google Cloud service account key file. This includes:

How to obtain:

1. Go to Google Cloud Console
2. Navigate to IAM & Admin → Service Accounts
3. Create or select a service account
4. Create a new key → JSON format
5. Copy the entire JSON content

project_id (Optional)

Override the project ID from the service account JSON if you want to query a different project.

Usage

The plugin provides two separate tools that can be used in Dify workflows:

Tool 1: BigQuery Query (Read Operations)

Use this tool to execute SELECT queries and retrieve data from BigQuery.

In Dify Workflows:

1. Add the "BigQuery Query" tool to your workflow
2. Configure the query parameter with your SELECT statement
3. Optionally set max_fetched_rows to limit result size
4. Connect the output to subsequent nodes

Parameters:

• query (string, required): The SELECT query to execute
• max_fetched_rows (number, optional): Maximum rows to return (default: 100)

Important: Only SELECT queries are allowed in this tool. Use the Mutation tool for data modifications.

Output:

• : Array of row data
• : Array of column names
• JSON message with formatted results

Tool 2: BigQuery Mutation (Write Operations)

Use this tool to execute INSERT, UPDATE, or DELETE queries to modify data in BigQuery.

In Dify Workflows:

1. Add the "BigQuery Mutation" tool to your workflow
2. Configure the query parameter with your INSERT/UPDATE/DELETE statement
3. Connect the output to subsequent nodes

Parameters:

• query (string, required): The INSERT, UPDATE, or DELETE query to execute

Important: Only INSERT, UPDATE, DELETE queries are allowed in this tool. Use the Query tool for SELECT operations.

Output:

• : Number of rows modified
• : Boolean indicating successful execution
• JSON message with mutation results

Example Queries

Query Tool Examples (SELECT)

Simple SELECT:

Query a Table:

Note: Use max_fetched_rows parameter to limit results returned to Dify

Aggregation Query:

Mutation Tool Examples (INSERT/UPDATE/DELETE)

INSERT Data:

UPDATE Data:

DELETE Data:

Output Formats

Query Tool Output:

1. Variables:

   • : Array of row tuples
   • : Array of column names

2. JSON Message:

Mutation Tool Output:

1. Variables:

   • : Number of rows modified (integer)

2. JSON Message:

Security Features

Tool Separation

• Query Tool: Restricted to SELECT operations only - rejects all modification queries
• Mutation Tool: Restricted to INSERT/UPDATE/DELETE operations only - rejects SELECT queries
• This separation ensures intentional data modifications and prevents accidental writes

Query Restrictions

• No Parameters: Query parameters are not allowed to prevent injection attacks
• SQL Validation: All queries are parsed and validated before execution
• Type Checking: SQL AST analysis ensures query types match tool capabilities

Cost Protection

• Query Cost Limit: Maximum 10GB of data scanned per query
• Per-Query Row Limits: Control result size for each individual query (Query tool only)
• Timeout Protection: Queries have timeout limits to prevent long-running operations

Development

Building from Source

Testing

See TESTING.md for detailed testing instructions.

Troubleshooting

Common Issues

Error: "Invalid JSON in service_account_json"

• Ensure you're pasting the complete, valid JSON from your service account key file
• Check for any truncated or modified content

Error: "Missing required fields in service account JSON"

• Verify your service account JSON contains all required fields: type, project_id, private_key_id, private_key, client_email

Error: "Only SELECT operations are allowed" (from Query tool)

• You're using the Query tool with a mutation query
• Use the BigQuery Mutation tool for INSERT, UPDATE, DELETE operations

Error: "Only INSERT, UPDATE, DELETE operations are allowed" (from Mutation tool)

• You're using the Mutation tool with a SELECT query
• Use the BigQuery Query tool for SELECT operations

Query timeout or exceeded quota

• Reduce query complexity
• Ensure your GCP project has sufficient BigQuery quota
• Check service account permissions

Architecture

The plugin is structured following Dify's plugin architecture:

Key Components

• Provider: Handles credential validation and configuration for both tools
• Query Tool: Implements SELECT query execution with row limiting
• Mutation Tool: Implements INSERT/UPDATE/DELETE operations with affected row tracking
• SQL Validation: Uses sqlglot for SQL parsing and type checking (shared by both tools)

Dependencies

• - Dify plugin SDK
• - Google BigQuery client
• - SQL parser and transpiler
• - Fast SQL parser

License

See LICENSE file for details.

Support

For issues, questions, or contributions, please visit:

• GitHub: https://github.com/david-pivonka/dify-bigquery-plugin

Privacy

See PRIVACY.md for information about data handling and privacy practices.