steampipe plugin install aws

Table: aws_glue_connection - Query AWS Glue Connections using SQL

The AWS Glue Connection is a component of AWS Glue which allows you to store and retrieve metadata related to your data sources, data targets, and transformations. It facilitates the management of data across multiple data stores by providing a unified view of your data. This enables AWS Glue to connect to your source and target databases, data warehouses, and data lakes for data extraction, transformation, and loading (ETL) processes.

Table Usage Guide

The aws_glue_connection table in Steampipe provides you with information about connections within AWS Glue. This table allows you, as a DevOps engineer, to query connection-specific details, including the connection name, the connection type, the physical connection requirements, and the connection properties. You can utilize this table to gather insights on connections, such as the type of connections, their properties, and the requirements for physical connections. The schema outlines the various attributes of the AWS Glue connection for you, including the catalog ID, creation time, last updated time, match criteria, and associated tags.

Examples

Basic info

Explore which AWS Glue connections are currently established to understand their type, creation time, and the region they're in. This can help in managing and optimizing the use of AWS resources.

select
name,
connection_type,
creation_time,
description,
region
from
aws_glue_connection;
select
name,
connection_type,
creation_time,
description,
region
from
aws_glue_connection;

List connection properties for JDBC connections

This query helps you examine the properties of JDBC connections, including connection URLs and SSL status. It's useful for managing and auditing your database connections, ensuring they are secure and set up correctly.

select
name,
connection_type,
connection_properties ->> 'JDBC_CONNECTION_URL' as connection_url,
connection_properties ->> 'JDBC_ENFORCE_SSL' as ssl_enabled,
creation_time
from
aws_glue_connection
where
connection_type = 'JDBC';
select
name,
connection_type,
json_extract(connection_properties, '$.JDBC_CONNECTION_URL') as connection_url,
json_extract(connection_properties, '$.JDBC_ENFORCE_SSL') as ssl_enabled,
creation_time
from
aws_glue_connection
where
connection_type = 'JDBC';

List mongodb connections with ssl disabled

Identify instances where MongoDB connections have SSL disabled to assess potential security vulnerabilities. This can be useful in maintaining secure data practices by pinpointing the specific connections that may require updating.

select
name,
connection_type,
connection_properties ->> 'CONNECTION_URL' as connection_url,
connection_properties ->> 'JDBC_ENFORCE_SSL' as ssl_enabled,
creation_time
from
aws_glue_connection
where
connection_type = 'JDBC'
and connection_properties ->> 'JDBC_ENFORCE_SSL' = 'false';
select
name,
connection_type,
json_extract(connection_properties, '$.CONNECTION_URL') as connection_url,
json_extract(connection_properties, '$.JDBC_ENFORCE_SSL') as ssl_enabled,
creation_time
from
aws_glue_connection
where
connection_type = 'JDBC'
and json_extract(connection_properties, '$.JDBC_ENFORCE_SSL') = 'false';

List connection vpc details

This query is useful to analyze the details of your AWS Glue connections in relation to their corresponding VPC subnets. It helps in assessing the configuration of physical connection requirements and understanding the link between different AWS resources.

select
c.name as connection_name,
s.vpc_id as vpc_id,
s.title as subnet_name,
physical_connection_requirements ->> 'SubnetId' as subnet_id,
physical_connection_requirements ->> 'AvailabilityZone' as availability_zone,
cidr_block,
physical_connection_requirements ->> 'SecurityGroupIdList' as security_group_ids
from
aws_glue_connection c
join aws_vpc_subnet s on physical_connection_requirements ->> 'SubnetId' = s.subnet_id;
select
c.name as connection_name,
s.vpc_id as vpc_id,
s.title as subnet_name,
json_extract(physical_connection_requirements, '$.SubnetId') as subnet_id,
json_extract(
physical_connection_requirements,
'$.AvailabilityZone'
) as availability_zone,
cidr_block,
json_extract(
physical_connection_requirements,
'$.SecurityGroupIdList'
) as security_group_ids
from
aws_glue_connection c
join aws_vpc_subnet s on json_extract(c.physical_connection_requirements, '$.SubnetId') = s.subnet_id;

Schema for aws_glue_connection

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
account_idtext=, !=, ~~, ~~*, !~~, !~~*The AWS Account ID in which the resource is located.
akasjsonbArray of globally unique identifier strings (also known as) for the resource.
arntextThe Amazon Resource Name (ARN) of the connection.
connection_propertiesjsonbThese key-value pairs define parameters for the connection.
connection_typetext=The type of the connection. Currently, SFTP is not supported.
creation_timetimestamp with time zoneThe time that this connection definition was created.
descriptiontextThe description of the connection.
last_updated_bytextThe user, group, or role that last updated this connection definition.
last_updated_timetimestamp with time zoneThe last time that this connection definition was updated.
match_criteriajsonbA list of criteria that can be used in selecting this connection.
nametext=The name of the connection definition.
partitiontextThe AWS partition in which the resource is located (aws, aws-cn, or aws-us-gov).
physical_connection_requirementsjsonbA map of physical connection requirements, such as virtual private cloud (VPC) and SecurityGroup, that are needed to make this connection successfully.
regiontextThe AWS Region in which the resource is located.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
titletextTitle of the resource.

Export

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- aws

You can pass the configuration to the command with the --config argument:

steampipe_export_aws --config '<your_config>' aws_glue_connection