Skip to content
AWS for Query.Farm

Query.Farm's Amazon Web Services (AWS) Integration

AWS Logo

Query.Farm’s AWS integration allows seamless interaction with the AWS API through SQL queries. By leveraging the efficiency of Arrow Flight, AWS data is effortlessly integrated into your DuckDB instance, enabling powerful querying, analysis, and transformation of AWS services using SQL.

Key Features

  • Access a wide range of AWS services and APIs with SQL, offering over 6,000 available tables.
  • Utilize unified SQL schemas for all AWS API data, eliminating the need for manual response parsing.
  • Benefit from DuckDB’s high-performance query engine for real-time data exploration.
  • Accelerate data access with Arrow Flight-optimized data transport.

Getting Started

  1. Install DuckDB version 1.1.1 or later.

  2. Install the Airport extension from Query.Farm via DuckDB.

    INSTALL 'airport' from community;
    LOAD 'airport';

  3. Create an account at Query.Farm for AWS to create an access token and setup AWS credentials/roles.

    Create Account

  4. Store the access token using the DuckDB secrets manager.

    CREATE PERSISTENT SECRET aws_secret (
     TYPE AIRPORT,
     auth_token '123456789abdef',
     scope 'grpc://aws.flights.query.farm:8080/'
    );

  5. Attach the connection.

    ATTACH 'aws' (TYPE AIRPORT, location 'grpc://aws.flights.query.farm:8080/');

  6. You’re ready to query!

    SELECT * from aws.sts.get_caller_identity;

Available AWS Services

Query.Farm’s AWS integration, you can query data from these all AWS services. Use the sidebar to explore each service’s tables and schema details, here are some popular services. In addition to having access to all of the AWS services, the integration can also query across accounts and across AWS Organizations, all in the same SQL statement.

S3

EC2

IAM

CloudWatch

SQS

Cost Explorer

Example Queries

Here are a few examples to help you get started:

List S3 Buckets

SELECT unnest(buckets, max_depth := 2) FROM aws_s3.buckets;

Query EC2 Instances

WITH instances as (
  SELECT
    UNNEST(instances, max_depth := 2),
    * EXCLUDE instances
  FROM aws.ec2.describe_instances
)
SELECT instance_id, launch_time, _aws_region, instance_type
FROM instances where state.name = 'running';

Monitor CloudWatch Metrics

SELECT
UNNEST(datapoints, max_depth:= 2)
FROM aws.cloudwatch.get_metric_statistics
WHERE
  metric_name = 'CpuUtilized' and
  namespace = 'ECS/ContainerInsights' and
  start_time = '2024-09-26'::timestamp_s and
  end_time = '2024-09-27'::timestamp_s and
  period = '60' and
  Statistics = ['Average'] and
  dimensions =[{'name': 'ClusterName', 'value': 'beta-database-flights'}];

Schema Information

Each AWS service exposes SQL tables representing the different resources and entities. Navigate through the sidebar to view detailed schemas for each service and discover all the available tables. These schemas are designed to match the AWS API structure closely, making it easy to work with familiar data types and resources.

Documentation & Support

Browse the sidebar to dive into individual AWS services, view schemas, and find more advanced query examples. For help or questions, check out our FAQ or contact support.