I am learning Snowflake right now, and the way FLATTEN() works is a bit counter intuitive. A simple query. SELECT raw:first_name::STRING AS FName, raw:last_name::STRING AS LName, f.value::STRING AS Skill FROM tbl, TABLE(FLATTEN(raw:Skills)) f ORDER BY raw:id::INT LIMIT 5; Elegantly, it…
I'm trying to query on the following JSON Blobs in MyTable that is varchar, however, JSONBlob2 has square brackets over the curly brackets that seem to be interfering with my query: MyTable JSONBlob JSONBlob2 {"a": "12345", "b": {"c":"567", "d":"llc"} }…
Snowflake table has a varchar column with following JSON array as value. [{"$":6394.2,"@":"decisionMetricValue","@id":"Month 1"},{"$":6672.25,"@":"decisionMetricValue","@id":"Month 2"},{"$":5643.05,"@":"decisionMetricValue","@id":"Month 3"},{"$":5564.79,"@":"decisionMetricValue","@id":"Month 4"},{"$":6966.75,"@":"decisionMetricValue","@id":"Month 5"},{"$":7605.15,"@":"decisionMetricValue","@id":"Month 6"}] Using snowflake sql, I would like to extract data in following format. Month1,Month2,Month3,Month4,Month5,Month6 6394.2,6672.25,5643.05,5564.79,6966.75,7605.15 I was trying to implement solution https://stackoverflow.com/a/77319223/20535715…
I want to be able to extract all values from json in snowflake contains "_sum". I have this format for example: {"260196":7877,"260206":2642,"260216":7620,"260226":7560,"_sum":25699} {"260196":9804,"260206":9804,"260216":9487,"260226":9411,"_sum":38506} I want this results: Total Value _sum 25699 _sum 38506 I try to used this: , TO_VARCHAR(GET_PATH(PARSE_JSON(x),…
We want to use Snowflake. As you maybe know, Snoflake is only available in specific regions on major hypersclaers like Microsoft Azure: https://docs.snowflake.com/en/user-guide/intro-regions#europe-middle-east-regions In Germany, there is no region. Next one is in the Netherlands. All our services are hosted…
Just had IT install Python 3.12 on my Windows machine. I do not have admin rights on my machine, which may or may not be important. During install, the following were done: Clicked "Add Python 3.n to Path" box. Went…
I have a container which needs to connect to snowflake using SSO. I have already seen the thread here; however it seems the solution there is causing me another error. When the authenticator is set to 'externalbrowser', the below message…
Using this cicd yml file in github. name: dbt Build and Push on: push: branches: - main permissions: contents: write jobs: build: runs-on: ubuntu-20.04 container: image: xemuliam/dbt:1.5 steps: - name: Check out code uses: actions/checkout@v3 run: | dbt debug dbt…
CREATE TABLE t (STUDENT int, SCORE int, DATE date) ; INSERT INTO t (STUDENT, SCORE, DATE) VALUES (1, 6, '2022-02-01 00:00:00'), (1, 2, '2022-03-12 00:00:00'), (1, 5, '2022-04-30 00:00:00'), (2, 2, '2022-04-12 00:00:00'), (2, 0, '2022-04-17 00:00:00'), (2, 7, '2022-05-08…
I am looking for SQL to Find Differences between columns and only till 12 rows and then sum the differences. For example this is the table I have TAB_PAYOUT. Column DIFFERENCE is calculated by substracting the value of column PAY…