Table: yml_key_value - Query Config YML Key Values using SQL
Config is a service that enables you to assess, audit, and evaluate the configurations of your config resources. It provides a real-time snapshot of your resource configurations and lets you monitor configuration changes over time. Config helps you to ensure that your resources comply with your corporate standards and best practices.
Table Usage Guide
The yml_key_value
table provides insights into key-value pairs within YAML files in Config. As a DevOps engineer, explore key-specific details through this table, including their corresponding values and associated metadata. Utilize it to uncover information about keys, such as their hierarchal structure, the relationships between keys, and the verification of key values.
Examples
The key_path
column's data type is
ltree, so all key_path
values are stored as dot-delimited label paths. This enables the use of the
usual comparison operators along with ltree
operators and functions which can
be used to match subpaths, find ancestors and descendants, and search arrays.
For all examples below, assume we're using the file invoice.yml
with the following configuration:
---receipt: Oz-Ware Purchase Invoicedate: 2012-08-06customer: first_name: Dorothy family_name: Gale# List of ordered itemsitems: - part_no: A4786 # item 1 description: Water Bucket (Filled) price: 1.47 quantity: 4 - part_no: E1628 # item 2 description: High Heeled "Ruby" Slippers size: 8 price: 133.7 quantity: 1bill-to: &id001street: | 123 Tornado Alley Suite 16city: East Centervillestate: KSship-to: *id001
Query a specific key-value pair
Explore a specific key-value pair in a YAML file to quickly identify a particular item's part number. This can be particularly useful for inventory management and tracking. You can query a specific key path to get its value:
select key_path, value as part_nofrom json_key_valuewhere path = '/Users/myuser/yml/invoice.yml' and key_path = 'items.0.part_no';
select key_path, value as part_nofrom json_key_valuewhere path = '/Users/myuser/yml/invoice.yml' and key_path = 'items.0.part_no';
+-----------------+-------+| key_path | value |+-----------------+-------+| items.0.part_no | A4786 |+-----------------+-------+
Query using comparison operators
Determine the areas in your system where certain keys are less than a specified value. This practical application can be useful in scenarios where you need to filter out specific segments of your data based on your set criteria.
The usual comparison operators, like <
, >
, <=
, and >=
work with ltree
columns.
For instance, you can use the <
operator to query all key paths that are before items
alphabetically:
select key_path, value as part_nofrom json_key_valuewhere path = '/Users/myuser/yml/invoice.yml' and key_path < 'items';
select key_path, value as part_nofrom json_key_valuewhere path = '/Users/myuser/yml/invoice.yml' and key_path < 'items';
+----------------------+----------------------+| key_path | part_no |+----------------------+----------------------+| bill_to | <null> || city | East Centerville || customer.family_name | Gale || customer.first_name | Dorothy || date | 2012-08-06T00:00:00Z |+----------------------+----------------------+
Query using path matching
Explore specific parts within an invoice file to identify their unique part numbers. This is particularly useful when you need to quickly locate and assess individual parts within a large inventory.
ltree
also supports additional operators like ~
which can be used to find all part_no
subkeys:
select key_path, value as part_nofrom json_key_valuewhere path = '/Users/myuser/yml/invoice.yml' and key_path ~ 'items.*.part_no';
Error: SQLite does not support regular expression matching with the ~ operator.
+-----------------+---------+| key_path | part_no |+-----------------+---------+| items.1.part_no | E1628 || items.0.part_no | A4786 |+-----------------+---------+
List descendants of a specific node
Determine the details associated with a specific customer in an invoice file. This is useful for gaining insights into the customer's information, such as their first and last names.
select key_path, valuefrom json_key_valuewhere path = '/Users/myuser/yml/invoice.yml' and key_path < @ 'customer';
Error: SQLite does not support the < @ operator used in PostgreSQL for array comparison.
+----------------------+---------+| key_path | value |+----------------------+---------+| customer.first_name | Dorothy || customer.family_name | Gale |+----------------------+---------+
Create a pivot table and search for a specific key
This example demonstrates how to organize and examine data from a YAML file, specifically the details of different items from an invoice. The query allows for the easy examination of specific item details such as part number, item name, size, quantity, and price. Additionally, it provides a way to pinpoint information for a particular item using its part number, making it a valuable tool for inventory management and financial tracking.
with items as ( select subpath(key_path, 0, 2) as item, subpath(key_path, 2, 3) as data, value from yml_key_value where path = '/Users/myuser/yml/invoice.yml' and key_path ~ 'items.*')select max( case when data = 'part_no' then value else null end ) as part_no, max( case when data = 'description' then value else null end ) as item_name, max( case when data = 'size' then value else null end ) as size, max( case when data = 'quantity' then value else null end ) as quantity, max( case when data = 'price' then value else null end ) as pricefrom itemsgroup by item;
Error: SQLite does not support array operations like subpathand '~' operator.
+---------+-----------------------------+--------+----------+-------+| part_no | item_name | size | quantity | price |+---------+-----------------------------+--------+----------+-------+| A4786 | Water Bucket (Filled) | <null> | 4 | 1.47 || E1628 | High Heeled "Ruby" Slippers | 8 | 1 | 133.7 |+---------+-----------------------------+--------+----------+-------+
You can also check the value for a particular key:
with items as ( select subpath(key_path, 0, 2) as item, subpath(key_path, 2, 3) as data, value from yml_key_value where path = '/Users/myuser/yml/invoice.yml' and key_path ~ 'items.*'),pivot_tables as ( select max( case when data = 'part_no' then value else null end ) as part_no, max( case when data = 'description' then value else null end ) as item_name, max( case when data = 'size' then value else null end ) as size, max( case when data = 'quantity' then value else null end ) as quantity, max( case when data = 'price' then value else null end ) as price from items group by item)select *from pivot_tableswhere part_no = 'E1628';
Error: SQLite does not support the subpath function which is used in the PostgreSQL query.
+---------+-----------------------------+--------+----------+-------+| part_no | item_name | size | quantity | price |+---------+-----------------------------+--------+----------+-------+| E1628 | High Heeled "Ruby" Slippers | 8 | 1 | 133.7 |+---------+-----------------------------+--------+----------+-------+
Casting column data for analysis
This query is used to restructure and analyze invoice data stored in a YAML file. It allows users to understand the details of each item in the invoice, such as the part number, item name, size, quantity, and price, by transforming the data into a more readable and analyzable format.
The value
column data type is text
, so you can easily cast it when required:
with items as ( select subpath(key_path, 0, 2) as item, subpath(key_path, 2, 3) as data, value from yml_key_value where path = '/Users/myuser/yml/invoice.yml' and key_path ~ 'items.*')select max( case when data = 'part_no' then value else null end ) as part_no, max( case when data = 'description' then value else null end ) as item_name, ( max( case when data = 'size' then value else null end ) ) :: integer as size, ( max( case when data = 'quantity' then value else null end ) ) :: integer as quantity, ( max( case when data = 'price' then value else null end ) ) :: float as pricefrom itemsgroup by item;
Error: SQLite does not support array operations like subpathand ~ used in the given PostgreSQL query.
+---------+-----------------------------+--------+----------+-------+| part_no | item_name | size | quantity | price |+---------+-----------------------------+--------+----------+-------+| A4786 | Water Bucket (Filled) | <null> | 4 | 1.47 || E1628 | High Heeled "Ruby" Slippers | 8 | 1 | 133.7 |+---------+-----------------------------+--------+----------+-------+
Schema for yml_key_value
Name | Type | Operators | Description |
---|---|---|---|
_ctx | jsonb | Steampipe context in JSON form, e.g. connection_name. | |
foot_comment | text | Specifies the comment following the node and before empty lines. | |
head_comment | text | Specifies the comment in the lines preceding the node and not separated by an empty line. | |
key_path | ltree | Specifies full path of a key in YML file. | |
keys | jsonb | The array representation of path of a key. | |
line_comment | text | Specifies the comment at the end of the line where the node is in. | |
path | text | = | Specifies the path of the YML file. |
pre_comments | jsonb | Specifies the comments added above a key. | |
start_column | bigint | Specifies the starting column of the value. | |
start_line | bigint | Specifies the line number where the value is located. | |
tag | text | Specifies the data type of the value. | |
value | text | Specifies the value of the corresponding key. |
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)" -- config
You can pass the configuration to the command with the --config
argument:
steampipe_export_config --config '<your_config>' yml_key_value