I have a web scraper dumping data into a variant column in a Snowflake database. This is acraping page data as then creates json arrays for various tables found within the page. Here is an example of the type of json i would find using a Soccer analogy: Ideally, i’d like the output of this to be a flat, relational
Tag: snowflake-cloud-data-platform
SnowFlake Convert_timezone function format mapping
Problem My 24 timezone codes are like EAT, ICT, NZT and I need to use the SnowFlake Convert_timezone function to convert the American Eastern time to the timezone that the records have. But SnowFlake …
How to perform indirect references into a variant column?
I’m determining the structure/query I would need to handle indirect references into a variant column (i.e. I want to extract different fields for different rows based on the definitions of another …
Snowflake to S3 with Header
Does anyone know of a way to export your data from Snowflake to an S3 file with a header? For example, I have this table: I want to export this data to a file that looks like this: … but I don’t see an option in the Snowflake documentation to do so. I tried a simple UNION ALL with the
Reducing granularity of historical status table?
I have a table with what are essentially historical logs for project tasks. Each row contains the ID for a project and the date of when one specific task was either started or ended. I need to reduce the grain so that all of the start/end times are in a single row (see IDs 1 and 2 in the image)
Partition by with condition statement
I have data of products that are sold by various shops. For some shops they are sold with discount mapped by PROMO_FLG. I would like to display two COUNT PARTITION columns. First displays all shops that thave this product (which is done) COUNT(DISTINCT STORE) OVER (PARTITION ITEM) would give is 10 Second one – which I seek – counts only
Last_Value returning the current value
I am not able to get the last value, rather it is just returning the same value with my code below in snowflake – does anyone have any idea? Is there something glaring wrong? select MNTH, …
Use Regex to Exclude any values that do not have a letter
For example, column below: My desired output would be: I tried this but no luck: UPDATED EDIT: Data Type = VARCHAR(16777216) Answer on snowflake this works: gives: And given REGEXP is an alias for RLIKE this also works given these behave like LIKE which is a non-greedy match, the wildcards are needed to match all the prior and after tokens.
Flatten data source in Snowflake from Array
I am trying to fix an array in a dataset. Currently, I have a data set that has a reference number to multiple different uuids. What I would like to do is flatten this out in Snowflake to make it so the reference number has separate row for each uuid. For example Should end up looking like: I just started
Running total of positive and negative numbers where the sum cannot go below zero
This is an SQL question. I have a column of numbers which can be positive or negative, and I’m trying to figure out a way to have a running sum of the column, but where the total cannot go below zero. I have tried a number of different window functions on this, but haven’t found a way to prevent the