# BigQuery Client

Execute SQL queries against Google BigQuery with full type safety and runtime validation.

## Methods

| Method                                      | Description                                                |
| ------------------------------------------- | ---------------------------------------------------------- |
| `query<T>(sql, schema, params?, metadata?)` | Execute a SELECT query and return validated, typed results |
| `execute(sql, params?, metadata?)`          | Execute a statement and return affected row count          |

## Usage

### Basic Query with Schema Validation

```typescript
import { api, z, bigquery } from "@superblocksteam/sdk-api";

// Integration ID from the integrations panel
const PROD_BIGQUERY = "a1b2c3d4-5678-90ab-cdef-bigquery0001";

const EventSchema = z.object({
  event_id: z.string(),
  event_name: z.string(),
  user_id: z.string(),
  timestamp: z.string(),
});

export default api({
  name: "GetEventCounts",
  integrations: {
    bigquery: bigquery(PROD_BIGQUERY),
  },
  input: z.object({
    event_name: z.string(),
  }),
  output: z.object({
    events: z.array(EventSchema),
  }),
  async run(ctx, { event_name }) {
    const events = await ctx.integrations.bigquery.query(
      `SELECT event_id, event_name, user_id, timestamp
       FROM \`project.dataset.events\`
       WHERE event_name = ?
       LIMIT 100`,
      EventSchema,
      [event_name],
    );

    return { events };
  },
});
```

The SDK currently binds BigQuery parameters from the positional `params` array.
Even though native BigQuery also supports named parameters like `@event_name`,
use `?` placeholders in SDK queries and pass values in array order.

### Executing INSERT, UPDATE, DELETE Statements

```typescript
// INSERT
await ctx.integrations.bigquery.execute(
  "INSERT INTO `project.dataset.metrics` (name, value, timestamp) VALUES (?, ?, CURRENT_TIMESTAMP())",
  ["cpu_usage", "75.5"],
);
```

### Analytics Query

```typescript
const AnalyticsSchema = z.object({
  date: z.string(),
  total_events: z.coerce.number(),
  unique_users: z.coerce.number(),
});

const analytics = await ctx.integrations.bigquery.query(
  `SELECT
    DATE(timestamp) as date,
    COUNT(*) as total_events,
    COUNT(DISTINCT user_id) as unique_users
  FROM \`project.dataset.events\`
  WHERE timestamp >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
  GROUP BY date
  ORDER BY date DESC`,
  AnalyticsSchema,
);
```

### Working with STRUCT and ARRAY

```typescript
const UserActivitySchema = z.object({
  user_id: z.string(),
  events: z.array(
    z.object({
      event_name: z.string(),
      count: z.coerce.number(),
    }),
  ),
});

const activity = await ctx.integrations.bigquery.query(
  `SELECT
    user_id,
    ARRAY_AGG(STRUCT(event_name, count)) as events
  FROM (
    SELECT user_id, event_name, COUNT(*) as count
    FROM \`project.dataset.events\`
    GROUP BY user_id, event_name
  )
  GROUP BY user_id`,
  UserActivitySchema,
);
```

## Trace Metadata

All methods accept an optional `metadata` parameter as the last argument for diagnostics labeling. See the [root SDK README](../../../README.md#trace-metadata) for details.

## Common Pitfalls

### SDK Parameters Are Positional

The SDK accepts an array of parameter values and binds them server-side in
order. When you need parameters, use `?` placeholders in the SQL text:

```typescript
// WRONG - Native BigQuery named parameter syntax is not the SDK contract
await ctx.integrations.bigquery.query(
  "SELECT * FROM `project.dataset.events` WHERE event_name = @event_name",
  EventSchema,
  ["signup"],
);

// CORRECT - Use positional params with ? placeholders
await ctx.integrations.bigquery.query(
  "SELECT * FROM `project.dataset.events` WHERE event_name = ?",
  EventSchema,
  ["signup"],
);
```

### Backtick Table References

BigQuery uses backticks for fully-qualified table names:

```typescript
// CORRECT
const query = "SELECT * FROM `project.dataset.table`";

// WRONG
const query = "SELECT * FROM project.dataset.table";
```

### INT64/NUMERIC Values Returned as Strings

```typescript
const schema = z.object({
  count: z.coerce.number(),
  amount: z.string().transform((val) => parseFloat(val)),
});
```

### Schema Parameter is Required

```typescript
// CORRECT - Schema is required
const events = await ctx.integrations.bigquery.query(
  "SELECT * FROM `project.dataset.events`",
  EventSchema,
);
```

### NULL Handling

```typescript
const schema = z.object({
  name: z.string(),
  description: z.string().nullable(),
});
```

### Query Costs

BigQuery charges by data scanned. Use partitions and column selection:

```typescript
// GOOD - Select specific columns and use partition filter
const query = `
  SELECT event_id, event_name
  FROM \`project.dataset.events\`
  WHERE _PARTITIONDATE = ?
`;
```

## Error Handling

```typescript
import { QueryValidationError } from "@superblocksteam/sdk-api";

try {
  const events = await ctx.integrations.bigquery.query(
    "SELECT * FROM `project.dataset.events`",
    EventSchema,
  );
} catch (error) {
  if (error instanceof QueryValidationError) {
    console.error("Row index:", error.details.rowIndex);
    console.error("Validation errors:", error.details.errors);
  }
}
```

## API Reference

- [BigQuery Documentation](https://cloud.google.com/bigquery/docs)
- [BigQuery SQL Reference](https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax)
- [BigQuery Data Types](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types)