I'm working with Twilio's Event Streams product. I'm successfully receiving events, parsing to models with Pydantic and all of this works.
Now I need to retain these historically. They're going to s3 and I plan on using Athena to query this data.
Here's the question -- the JSON schema for CallSummary events is nested and complex. Before I tediously create a CREATE TABLE statement for this model, is there an easier approach I should be following?
For reference, I'm building the Python models using datamodel-codegen (link) like this:
curl -s https://events-schemas.twilio.com/VoiceInsights.CallSummary/1 > call_summary_event.json
datamodel-codegen --input call_summary_event.json --input-file-type jsonschema --output call_summary_model.py
Using 1 call summary record and hive-json-schema (from quux00), I can get about half of the fields:
java -cp target/json-hive-schema-1.0.jar net.thornydev.JsonHiveSchema CA0000123.json
CREATE TABLE x (
metrics array<struct<account_sid:string, call_sid:string, carrier_edge:null, client_edge:null, direction:string, edge:string, sdk_edge:null, sip_edge:struct<codec:int, codec_name:string, cumulative:struct<jitter:struct<avg:double, max:double>, packets_lost:int, packets_sent:int>, interval:struct<jitter:struct<avg:double, max:double>, packets_loss_percentage:double, packets_lost:int, packets_sent:int>, metadata:struct<edge_location:string, region:string, twilio_ip:string>>, timestamp:string>>)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe';
Unfortunately, much of the data in my call summary json records are not populated, so I can't easily use a script like this to convert the sample data to CREATE TABLE with nested structs.
Before I go about doing this further, is there something I could use to go from the published JSON schema straight to Hive DDL?