![]() The short version is that a view can turn query results into a table. ![]() I also added a _v to the view name to visually indicate it is a view. This query also is only sharing a relevant data set to the view, and not the entire raw data of the hook itself. See the screenshot and query results below, it displays a human-readable time stamp now. NOTE: Jamf Pro Webhooks use epoch timestamps, so I decided to type cast it as more readable time stamp. Since we already have our table from before that worked, we can use like toĬREATE OR REPLACE VIEW JAMF_EVENTS_CHECKINS_V AS SELECT JSON_DATA : "event" : "computer" : "building" :: VARCHAR AS BUILDING, JSON_DATA : "event" : "computer" : "department" :: VARCHAR AS DEPARTMENT, JSON_DATA : "event" : "computer" : "emailAddress" :: VARCHAR AS EMAIL, JSON_DATA : "event" : "computer" : "realName" :: VARCHAR AS FULL_NAME, JSON_DATA : "webhook" : "eventTimestamp" :: VARCHAR :: TIMESTAMP_LTZ AS TIME FROM DB. It would not make a ton of sense to toss two or more different types of hook data sets in the same table.Įvent data is about capturing what events are taking place and mixing two different event types has no benefit and would We will first create a new table, just like our previous one, and this time we will use it for check-in hook eventsįrom Jamf Pro. With data like Jamf Pro Webhooks, you probably want to organize your data in a way where each event type is in its own Organizing Your Data and Leveraging Views It is really that simple andĪt the same time powerful. and, you don’t have to hold down the shift key a ton. It is simply one single : to tell Snowflake to interpret the rest as JSON/Variant. No big deal, right? The syntax is pretty simple. In the above query and results you can see that Snowflake can just natively parse raw JSON/Variant data stored in a column. Let’s check our work but running a SELECT * on our table and see what sort of data we get back. ![]() Snowflake has a built in parse_jsonįunction that will interpret the string as JSON data, and produce it as a variant data type I just picked a few random examples of the hooksįrom Jamf’s website and reran the above query. I repeated this process a few times to get several rows of data. INSERT INTO JAMF_EVENTS_RAW ( DATE, JSON_DATA ) SELECT current_timestamp (), parse_json ( $$$$ ) Let’s create a table first, so we can insert some data into it: ![]() So, lets get started! Create A table with JSON Data JSON data in raw form right in your database. Snowflake can parse JSON natively, meaning you can store Will just use their examples in Snowflake for this blog post. Since Jamf provides example Webhook Data in their online documentation, we Lifting off of your plate! Jamf Webhooks Right to Snowflake Tables No database configurations to deploy, so all you have to do is use the product and let Snowflake take all of that heavy Scale of the cloud, meaning IT & Ops folks don’t ever have to worry about managing the service and platform. The best part as an IT & Ops professional is that Snowflake uses the power and Query language used here, just simply SQL. This is another great thing about Snowflake is that you can use all the SQL experience you have gained over the years and apply it right to the platform. So, you might already be familiar enough with SQL. Things like: MySQL, Postgres, Oracle, Microsoft SQL, or another SQL based database. IT and Operations folks have also most likely dealt with some sort of SQL based database at some point in their career. Just ship the JSON data as is right to the platform. This allows IT and Operations people to ingest JSON data in their data pipelines without having to transform Snowflake can store semi-structured data in columns natively using the variantĭata type. You will work with JSON data most likely at some point. Like REST APIs, so if you haven’t worked with JSON JSON documents are very common all over tech. This post will focus on some basic ways one can work with data with in Snowflake.įor this blog I will be focusing on JSON data stored in Snowflake columns. Data has always been very important, and Snowflake just makes using the data a tonĮasier than we have ever had before. You may click that link to read it first. Welcome to Part II of my miniseries blog around Snowflake with IT & Ops Data.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |