aq - Query AWS resources with SQL
aq allows you to query your AWS resources (EC2 instances, S3 buckets, etc.) with plain SQL.
But why?
Fun, mostly fun. But see sample queries below for useful queries that can be performed with aq.
Usage
Usage:
aq [options]
aq [options] <query>
Options:
--table-cache-ttl=<seconds> number of seconds to cache the tables
before we update them from AWS again [default: 300]
-v, --verbose enable verbose logging
Running aq without specifying any query will start a REPL to run your queries interactively.
Sample queries
One of the most important benefit of being able to query which SQL is aggregation and join, which can be very complicated or even impossible to do with AWS CLI.
To count how many running instances per instance type
> SELECT instance_type, count(*) count FROM ec2_instances WHERE state->'Name' = 'running' GROUP BY instance_type ORDER BY count DESC +-----------------+---------+ | instance_type | count | |-----------------+---------| | m4.2xlarge | 15 | | m4.xlarge | 6 | | r3.8xlarge | 6 | +-----------------+---------+
Find instances with largest attached EBS volumes size
> SELECT i.id, i.tags->'Name' name, count(v.id) vols, sum(v.size) size, sum(v.iops) iops FROM ec2_instances i JOIN ec2_volumes v ON v.attachments -> 0 -> 'InstanceId' = i.id GROUP BY i.id ORDER BY size DESC LIMIT 3 +------------+-----------+--------+--------+--------+ | id | name | vols | size | iops | |------------+-----------+--------+--------+--------| | i-12345678 | foo | 4 | 2000 | 4500 | | i-12345679 | bar | 2 | 332 | 1000 | | i-12345687 | blah | 1 | 320 | 960 | +------------+-----------+--------+--------+--------+
Find instances that allows access to port 22 in their security groups
> SELECT i.id, i.tags->'Name' name, sg.group_name FROM ec2_instances i JOIN ec2_security_groups sg ON instr(i.security_groups, sg.id) WHERE instr(sg.ip_permissions, '"ToPort": 22,') +------------+-----------+---------------------+ | id | name | group_name | |------------+-----------+---------------------| | i-foobar78 | foobar | launch-wizard-1 | | i-foobar87 | blah | launch-wizard-2 | +------------+-----------+---------------------+
AWS Credential
aq relies on boto3 for AWS API access so all the credential configuration mechanisms of boto3 will work. If you are using the AWS CLI then you can use aq without any further configurations.
Available tables
AWS resources are specified as table names in <resource>_<collection> format with:
- resource
one of the resources defined in boto3: ec2, s3, iam, etc.
- collection
one of the resource’s collections defined in boto3: instances, images, etc.
An optional schema (i.e. database) name can be used to specify the AWS region to query. If you don’t specify the schema name then boto’s default region will be used.
-- to count the number of ec2 instances in AWS Singapore region SELECT count(*) FROM ap_southeast_1.ec2_instances
Note that the region name is specified using underscore (ap_southeast_1) instead of dash (ap-southeast-1).
At the moment the full table list for AWS us_east_1 region is
cloudformation_stacks |
cloudwatch_alarms |
cloudwatch_metrics |
dynamodb_tables |
ec2_classic_addresses |
ec2_dhcp_options_sets |
ec2_images |
ec2_instances |
ec2_internet_gateways |
ec2_key_pairs |
ec2_network_acls |
ec2_network_interfaces |
ec2_placement_groups |
ec2_route_tables |
ec2_security_groups |
ec2_snapshots |
ec2_subnets |
ec2_volumes |
ec2_vpc_addresses |
ec2_vpc_peering_connections |
ec2_vpcs |
glacier_vaults |
iam_groups |
iam_instance_profiles |
iam_policies |
iam_roles |
iam_saml_providers |
iam_server_certificates |
iam_users |
iam_virtual_mfa_devices |
opsworks_stacks |
s3_buckets |
sns_platform_applications |
sns_subscriptions |
sns_topics |
sqs_queues |
Query with structured value
Quite a number of resource contain structured value (e.g. instance tags) that cannot be use directly in SQL. We keep and present these values as JSON serialized string and add a new operator -> to make querying on them easier. The -> (replaced to json_get before execution) can be used to access an object field, object->'fieldName', or access an array item, array->index:
> SELECT '{"foo": "bar"}' -> 'foo'
+-------------------------------------+
| json_get('{"foo": "bar"}', 'foo') |
|-------------------------------------|
| bar |
+-------------------------------------+
> SELECT '["foo", "bar", "blah"]' -> 1
+--------------+
| json_get(' |
|--------------|
| bar |
+--------------+
Install
pip install aq
Tests (with nose)
nosetests
Metadata
Release files for aq 0.1.1
For a detailed explanation of source distributions (sdists) and built distributions (wheels), please see the package formats documentation.
Source distribution (sdist)
| File | Size | Uploaded | Provenance | Actions |
|---|---|---|---|---|
| aq-0.1.1.tar.gz | 12.6 kB | Without provenance | Details for aq-0.1.1.tar.gz |
Built distribution (wheel)
| File | Interpreter | ABI | Platform | Actions Reset |
|---|---|---|---|---|
| aq-0.1.1-py2.py3-none-any.whl | Python 2, Python 3 | none | any | Details for aq-0.1.1-py2.py3-none-any.whl |
Total release size: 29.8 kB
Release files / aq-0.1.1.tar.gz
| Download URL | aq-0.1.1.tar.gz |
|---|---|
| Size | 12.6 kB |
| Tags | Source |
|
SHA-256 checksum How to use checksums |
fed0c2f633007faee1653c23f144b86672c328c4d71c83509fab789c77591ebc
|
|
BLAKE2b-256 checksum How to use checksums |
0bf62ad1a9e110c5f2860d55c920eda140bbdee5d7ed499d4e86233f1d39b758
|
| Upload date | |
|
Uploaded using Trusted Publishing? What is trusted publishing? |
No |
Release files / aq-0.1.1-py2.py3-none-any.whl
| Download URL | aq-0.1.1-py2.py3-none-any.whl |
|---|---|
| Size | 17.2 kB |
| Tags | Python 2 Python 3 |
|
SHA-256 checksum How to use checksums |
c4765080604c3a22f850ecf990b3585bd42e3fc03b23f1cac33ab5ca343ffcef
|
|
BLAKE2b-256 checksum How to use checksums |
16332b436faffd09b45b3f274fc1464b8a7e22acf01b658f3b5a904f38bfab12
|
| Upload date | |
|
Uploaded using Trusted Publishing? What is trusted publishing? |
No |
Security for aq 0.1.1
Provenance
Provenance describes where a file came from. On PyPI, provenance is shared via attestations, which provide a verifiable record of the build or publishing details. View details, limitations and caveats.
No provenance found
None of the files in this release were uploaded with provenance metadata.
A note on package security
PyPI is a repository for distributing packages, not a service for auditing them. While third-party security researchers and our staff do their best to detect and remove malicious packages, PyPI cannot guarantee the quality, security or suitability of any given package.
To learn more about protecting yourself from malicious actors, visit our package security documentation.