# Arrow Flight SQL

## Goal

Connect an Arrow Flight SQL client to Altertable over TLS, run a query, and confirm that the client can stream Arrow record batches.

## Prerequisites

- Your [lakehouse username and password](/docs/lakehouse/authentication.md).
- An Arrow Flight SQL-compatible client.
- Access to at least one table you can query.

## Steps

### 1. Configure the endpoint

| Hostname               | Port          |
| ---------------------- | ------------- |
| `flight.altertable.ai` | 443 (TLS/SSL) |

[Arrow Flight SQL](https://arrow.apache.org/docs/format/FlightSql.html) provides the highest-performance interface for streaming large analytical datasets between Altertable and your tools. Built on Apache Arrow's columnar format, it's ideal for:

- **Analytics tools** like dbt, Airbyte, and custom data pipelines
- **Large result sets** that benefit from efficient columnar data transfer
- **High-throughput workloads** requiring minimal serialization overhead

### 2. Configure authentication

Use your Altertable credentials with any Arrow Flight SQL client:

- **Username**: Your lakehouse username
- **Password**: Your lakehouse password

### 3. Run your first query

### Arrow Flight SQL

```python
# Altertable exposes a native Arrow Flight SQL endpoint.
# Connect any Arrow Flight SQL-compatible client — no extra setup needed.

# Python (altertable-flightsql-python)
from altertable_flightsql import Client

# Connect to flight.altertable.ai:443 w/ TLS/SSL
with Client(username="{{LAKEHOUSE_USERNAME}}", password="{{LAKEHOUSE_PASSWORD}}") as client:
    # Execute a query
    reader = client.query("SELECT * FROM users WHERE age > 18")

    # Process results
    for batch in reader:
        df = batch.data.to_pandas()
        print(df)
```

### 4. Use the Python client

Altertable provides an official Python client library ([`altertable-flightsql`](https://github.com/altertable-ai/altertable-flightsql-python)) that handles authentication, connection management, queries, prepared statements, transactions, and metadata operations.

Install the package:

```bash
pip install altertable-flightsql
```

#### Prepared statements

Prepare a statement once and execute it multiple times with different parameters:

```python
with client.prepare("SELECT * FROM users WHERE id = $id") as stmt:
    result = stmt.query(parameters={"id": 1})
    for batch in result:
        print(batch.data.to_pandas())
```

#### Transactions

Execute multiple statements atomically:

```python
with client.begin_transaction():
    client.execute("INSERT INTO users ...")
    client.execute("UPDATE accounts ...")
```

#### Metadata queries

Discover catalogs, schemas, and tables:

```python
catalogs = client.get_catalogs()
schemas = client.get_schemas(catalog="my_db")
tables = client.get_tables(catalog="my_db", schema_pattern="main")
```

For more details, examples, and advanced usage, see the [GitHub repository](https://github.com/altertable-ai/altertable-flightsql-python).

### 5. Connect another client

Any Arrow Flight SQL-compatible client can connect to `flight.altertable.ai:443` with TLS.

**Python ADBC**

```python
import adbc_driver_flightsql.dbapi as flight_sql

conn = flight_sql.connect(
uri="grpc+tls://flight.altertable.ai:443",
db_kwargs={
"username": "{{LAKEHOUSE_USERNAME}}",
"password": "{{LAKEHOUSE_PASSWORD}}"
}
)

cursor = conn.cursor()
cursor.execute("SELECT * FROM your_database.your_schema.your_table LIMIT 10")
results = cursor.fetchall()
```

## Verification

Run a query that does not depend on an existing table:

```python
from altertable_flightsql import Client

with Client(
    username="{{LAKEHOUSE_USERNAME}}",
    password="{{LAKEHOUSE_PASSWORD}}",
) as client:
    for batch in client.query("SELECT 1 AS connected"):
        print(batch.data.to_pandas())
```

The result should contain one `connected` column with the value `1`.

## Troubleshooting

- **Authentication fails:** copy the lakehouse username and password from the same environment again.
- **The TLS connection fails:** connect to `flight.altertable.ai:443` and keep TLS enabled.
- **A table cannot be found:** use `get_catalogs()`, `get_schemas()`, and `get_tables()` to confirm its fully qualified name.
- **A generic client cannot connect:** confirm that it supports Arrow Flight SQL rather than only the Arrow in-memory format.

## Next steps

- [Query with the HTTP API](/docs/query-data/http-api.md) when HTTPS and JSONL fit the integration better.
- [Connect a BI tool](/docs/query-data/integrations.md) through Arrow Flight SQL or the Postgres adapter.
- See the [Python client repository](https://github.com/altertable-ai/altertable-flightsql-python) for advanced examples.