Using OCI data (pt2) – Loading the data

In the previous post I wrote about how to get access to the costing data, but I didn’t really talk about how to get that data into the database we were using for the actual application. As I mentioned, we’re going to use an “always-free” autonomous database, which also comes with Oracle APEX on top of it, so you can build and play with this from any tenancy (even a commercial one) but at no actual cost

The costing data comes in 2 separate files, a costing an a usage file, both provided as CSV’s, and I covered getting this information into the database, and the challenges with the “variable size” file. As I mentioned, this references every entitle by it’s OCID, and what you want are some friendly names. I mentioned that you could use the structured query, but thought I’d go into more detail here.

The structured query is effectively the same query which is executed from the OCI web console.

It provides a very quick response from your query, but it’s a limited subset of the data. By default, a structure query returns only the following values:

• availability-domain
• compartment-id (ocid)
• defined-tags
• display-name
• freeform-tags
• identifier (the ocid of this entity)
• identity-context
• lifecycle-state
• resource-type
• search-context
• system-tags
• time-created

This information might not be use in a lot of scenario’s, but for what we want in this case we have a quick query, which could return all of the real names for each entity, based on it’s OCID. The only challenge with this query is that it’s limited to 1,000 records. If you have 1,001 entries in your system then is pages the output.

To query paged output I’ll force my query to only return 1 row at a time (using the –limit 1 syntax on the query), to demonstrate how to see the 2nd, 3rd, 4th etc page.

To get the first row from my query, to return all the data for all instance resources, I’d use this query:

{ “data”: { “items”: [ { “additional-details”: {}, “availability-domain”: “PldG:UK-LONDON-1-AD-1”, “compartment-id”: “ocid1.compartment.oc1..aaaaaaaafg2t5frzukbpvvqpoupmr6sp5agid5rsidwlg5pthfcn4cns4eaa”, “defined-tags”: { “Budget”: { “Project”: “Belgium-HCM” }, “Oracle-Tags”: { “CreatedBy”: “oracleidentitycloudservice/mikael.de.pret@capgemini.com”, “CreatedOn”: “2024-04-09T15:14:29.204Z” } }, “display-name”: “NOTRoot-Script”, “freeform-tags”: {}, “identifier”: “ocid1.instance.oc1.uk-london-1.anwgiljtj5n3sfycunsn5xqglojw7grrza4mbmjlrmszz4dzenx736muuucq”, “identity-context”: {}, “lifecycle-state”: “STOPPED”, “resource-type”: “Instance”, “search-context”: null, “system-tags”: {}, “time-created”: “2024-04-09T15:14:29.666000+00:00” } ] }, “opc-next-page”: “eyJhbGciOiJwiOlwiNGU3ZjM3NGQtOWJkNC00MG…….MTI4Nh67JZJVaz3jFVoj8” }

You can see it’s output the records, and limited it to 1 entry. At the bottom of the returned JSON it’s included the opc-next-page flag (I have truncated this, but it’s a long string of characters).

To get page2, execute the same query as above, but use the –page flag, with the “opc-next-page” value in double quotes

jason_lest@cloudshell:~ (uk-london-1)$ oci search resource structured-search –query-text “query instance resources” –limit 1 –page “eyJhbGciOiJIUzI1NiJ9.eyJpc3MiOiJvY2lfcXVlcnkiLCJvY2lfbSI6IntcInZcIjozLFwic1wiOlwiNGU3ZjM3NGQtOWJkNC00MGYxLTlkZTgtODg3NjIwYzIxOWZjXCIsXCJuXCI6MSxcInBcIjpcIntcXFwidlxcXCI6MSxcXFwic3ZcXFwiOlsxNzEyNjc1NjY5NjY2LFxcXCJvY2lkMS5pbnN0YW5jZS5vYzEudWstbG9uZG9uLTEuYW53Z2lsanRqNW4zc2Z5Y3Vuc241eHFnbG9qdzdncnJ6YTRtYm1qbHJtc3p6NGR6ZW54NzM2bXV1dWNxXFxcIl19XCIsXCJ0XCI6XCJORVhUXCJ9Iiwib2NpX3MiOiIwYjJlY2UzOTQ2NjNkNzQxNzg0ZTYzZDgyMGMwN2Q0M2I4YjczYWUzNzJjODAwYWU3ODljNDQ3ODc2YzkyMDU1Iiwib2NpX2siOiJrXzMiLCJleHAiOjE3MTI4NDAyNDJ9.4AxCsVpI2l49qANyLFvkftfCfEIh67JZJVaz3jFVoj8” { “data”: { “items”: [ { “additional-details”: {}, “availability-domain”: “PldG:UK-LONDON-1-AD-3”, “compartment-id”: “ocid1.compartment.oc1..aaaaaaaanysrusnsyuf5ia2hpxwtg6npxs2dcvkxc3i5vzpbhidzxdavu7aa”, “defined-tags”: { “Budget”: { “Project”: “RETAIL” }, “Oracle-Tags”: { “CreatedBy”: “oracleidentitycloudservice/ravi-theja.n@capgemini.com”, “CreatedOn”: “2024-03-07T10:06:55.314Z” } }, “display-name”: “cgmomappdb1902stdby”, “freeform-tags”: {}, “identifier”: “ocid1.instance.oc1.uk-london-1.anwgiljsj5n3sfyc5anpgaqxmeycx4hkkvgok6b3cvzr5ioqvr6udjtrgiqa”, “identity-context”: {}, “lifecycle-state”: “RUNNING”, “resource-type”: “Instance”, “search-context”: null, “system-tags”: {}, “time-created”: “2024-03-07T10:06:55.901000+00:00” } ] }, “opc-next-page”: “eyJhbGciOiJIUzI1NiJ9.eyJpc3MiOiJvY2lfcXVlcnkiLCJvY2lfbSI6IntcInZcIjozLFwic1wiOlwiNGU3ZjM3NGQtOWJkNC00MGYxLTlkZTgtODg3NjIwYzIxOWZjXCIsXCJuXCI6MixcInBcIjpcIntcXFwidlxcXCI6MSxcXFwic3ZcXFwiOlsxNzA5ODA2MDE1OTAxLFxcXCJvY2lkMS5pbnN0YW5jZS5vYzEudWstbG9uZG9uLTEuYW53Z2lsanNqNW4zc2Z5YzVhbnBnYXF4bWV5Y3g0aGtrdmdvazZiM2N2enI1aW9xdnI2dWRqdHJnaXFhXFxcIl19XCIsXCJ0XCI6XCJORVhUXCJ9Iiwib2NpX3MiOiIwYjJlY2UzOTQ2NjNkNzQxNzg0ZTYzZDgyMGMwN2Q0M2I4YjczYWUzNzJjODAwYWU3ODljNDQ3ODc2YzkyMDU1Iiwib2NpX2siOiJrXzMiLCJleHAiOjE3MTI4NDA0NDN9.15PYwNzSWo76f_MXQt5u78UR6YlisjygcAp6sivL2Cw” }

Obviously you wouldn’t do this 1 page at a time normally, but you see that the second page also includes a link to the 3rd page, and so on. Since the query could potentially return the data in a different order each time, OCI caches your results to make sure that your calls are consistent if you;re using the –page flag, but only for a relatively short period of time.

WARNING: When you include a column filter with this query (see my earlier posts), it only returns the specific data you specify. That means it wont include the “opc-next-page” field if it’s not included in your filter.

Now we have the JSON output, what do you do with it ? One way it to import this data “as-is” into the database, and then use the SELECT query to interrogate the JSON output directly. The other alternative is to covert this data into insert statements at the O/S level, and then insert those records into the database then. The first method does require you to have a good understand of a JSON model, but for this data it’s pretty simple as it’s only a single layer. The second method is all about doing clever things with the likes of “sed” and “awk” to reformat the output data.

I will cover both of these in the next 2 blogs (3a & 3b in this series), so you can choose the best way for you of getting that data.

However, the idea is that if you can extract the OCID and the DISPLAY-NAME from this data (and I put them into a table called “FRIENDLY_NAME”) then you can cross reference them through a simple “WHERE” clause and display the user-friendly name for each record, rather than the OCID which isn’t of much use by itself.