Parsing YAML to create dynamic queries in shell

Question

I am very new to scripting, and I have been tasked with figuring out a way to script a shell script that will create SQL queries based on a provided YAML document. I could trace down the yq parser to use; however, I am stuck on how to use individual values from the YAML children nodes. Sample YAML that I need to parse is:

config:
  - system1:
      database_name: database1
      port: '1234'
      table:
        - name: table1
          values: 'table_id, value1, 30, randomValue'
        - name: table2
          values: 'table_id, value1, randomValue, randomValue, value2'
        - name: table2
          values: 'table_id, value2, randomValue, randomValue'
  - system2:
      database_name: database2
      port: '12345'
      table:
        - name: table4
          values: 'table_id, value3, 30, randomValue'
        - name: table5
          values: 'table_id, randomValue, randomValue, value4'
        - name: table6
          values: 'table_id, value3, value4'

The script that I am trying to write currently is nothing more than SELECT statements, but it will evolve later:

# This will later be used to create insert/update queries based on the values so 
# will be needing a handle for that too.
psql -h localhost -p $PORT -d $DATABASE << EOF
select * from $TABLE LIMIT 10;
EOF

If possible, without sounding needy, I would hope to see suggestions on how I can use yq library, given that it supports a bunch of operations that I will need to do to this script once the SQL part is completed.

I apologise if the question is too stupid, it would be my first time on shell scripting.

I am using Ubuntu 18.4 Distribution if that's relevant.

Answer 1

Using Andrey Kislyuk's yq (a YAML-aware wrapper around the JSON processor jq) rather than Mike Farah's yq, you can create the shell statements needed to execute the queries like so:

yq -r '
    .config[] |
    map(
        @sh "psql -h localhost -p \(.port) -d \(.database_name) <<END_SQL",
        (.table | map("SELECT \(.values) FROM \(.name) LIMIT 10;"))[],
        "END_SQL"
    )[]' file

or, if you don't like using map:

yq -r '
    .config[][] |
    @sh "psql -h localhost -p \(.port) -d \(.database_name) <<END_SQL",
    (.table[] | "SELECT \(.values) FROM \(.name) LIMIT 10;"), 
    "END_SQL"' file

This iterates over the config array, creating one psql command for each element. The psql command gets its -p and -d option arguments from the entry's port and database_name values (quoted for the shell using the @sh output operator).

The actual SQL statements are given to the command through a here-document redirection, delimited by the arbitrary chosen string END_SQL. The statements use the values value as-is for the fields that need to be extracted and the name value as the table's name whence to extract the fields. No special quoting is performed on these values.

Given the data from the question, the above command generates the following shell code:

psql -h localhost -p '1234' -d 'database1' <<END_SQL
SELECT table_id, value1, 30, randomValue FROM table1 LIMIT 10;
SELECT table_id, value1, randomValue, randomValue, value2 FROM table2 LIMIT 10;
SELECT table_id, value2, randomValue, randomValue FROM table2 LIMIT 10;
END_SQL
psql -h localhost -p '12345' -d 'database2' <<END_SQL
SELECT table_id, value3, 30, randomValue FROM table4 LIMIT 10;
SELECT table_id, randomValue, randomValue, value4 FROM table5 LIMIT 10;
SELECT table_id, value3, value4 FROM table6 LIMIT 10;
END_SQL

You may then execute this code by either piping it to sh -s, or by evaluating it with eval.