950 questions
1
vote
2
answers
35
views
Unpivot snowflake table with pivot column alias
I got the following example of how to unpivot Snowflake tables:
SELECT *
FROM monthly_sales
UNPIVOT (sales FOR month IN (jan, feb, mar, apr))
ORDER BY empid;
This is very nice, but I need the ...
- 5,450
0
votes
2
answers
38
views
how to find year_month_date from a string in snowflake
I am using snowflake to get some data. I have one requirement that i want to extract year_month_day from a string.
a string would be 'IN_2001_02_23_guid' and I want to extract 20010223 from this ...
- 527
0
votes
0
answers
39
views
Extract Data from an API and load it in to snowflake as table and finally connect those tables to power bi for visualiztion
I want to use this api documentation from this link https://api.sproutsocial.com/docs/
I want to store that data as snwoflake table
Step1: I was able to extract data for all the profiles in the url i....
0
votes
1
answer
76
views
Unable to perform BulkInsert of records into snowflake tables
public async Task<int> BulkInsertAsync<T>(string tableName, IEnumerable<T> data, int batchSize = 1000)
{
int totalRowsAffected = 0;
var batches = data.Batch(batchSize);
...
- 9
0
votes
0
answers
47
views
Snowpark filter function does not work correctly
I am trying to filter a Snowpark df with string value. But end result numbers are nonsense.
my_df = my_df.withColumn("MY_COLUMN", trim(F.col("MY_COLUMN")))
my_df.groupBy(F.col(&...
- 46
0
votes
2
answers
216
views
Is it possible to know last time a Row was updated in a table or view in Snowflake?
I’m working on an implementation of a view in Snowflake. I want to know when was the last time that a single row was updated. Is there a column from Snowflake or maybe a way to do that?
For example. I ...
- 507
0
votes
1
answer
152
views
How to revoke warehouse priviledge for role in Snowflake
I am trying to run query while selecting role AP_KPO_ANL with warehouse OP_WH. But now i created another new warehouse and grant role access to this new warehouse.
Now i want to revoke access for ...
- 3,859
0
votes
2
answers
76
views
Issue in getting snowflake view definition
Is there a way to get view definitions from snowflake DB, I am trying the following options.
USE DADATABSE <<DBNAME>>
USE SCHEMA <<SCHEMANAME>>
SHOW VIEWS;
I need only "...
- 239
0
votes
1
answer
82
views
Getting error while connecting Tableau desktop with Snowflake server
I am trying to connect to the Snowflake server via Tableau desktop using the role RO.
While pulling a table from the Snowflake server I am getting the below error:
Unable to complete action Invalid ...
0
votes
1
answer
34
views
Trying to create a stored procedure in snowflake but unable to execute the procedure
I have created two tables Sales_Data and Vendor_type and a view Sales_Summary and a stored procedure.
When calling the query I am getting this error:
Error: 'STATEMENT_ERROR' on line 3 at position 4 :...
0
votes
0
answers
33
views
Snowflake query to aggregate data using pivot values
I have the following table with array values in rows which needs to be aggregated using different timestamp level in snowflake
Bill_id
BillStarttime
Bill_Submitted
Billpaid
Billpaidtime
...
- 101
0
votes
0
answers
64
views
How to read the schema of the delta table in snowflake
In snowflake I want to understand, if there is a possibility to get the schema (column names and dataype) of the delta tables in ADLS.
I 'm trying to create a DDL structure ICEBERG Table, and DDL view ...
- 21
0
votes
0
answers
27
views
Extracting and Joining Variable-Length Account Numbers from Unstructured Strings using Snowflake
Problem:
I have two tables in Snowflake: a dimension table with account information and a fact table where the account numbers are embedded as part of a string in a PARAM_VALUE column of type string. ...
- 301
0
votes
0
answers
62
views
How to implement Snowflake streams in Snowflake native app
I am trying to implement a stream in Snowflake native app to the CDC on the consumer's table, which I got it with reference variable from manifest.yml file.
I am trying to create a stream as follows:
...
0
votes
3
answers
68
views
Preceding rows sum and subtraction using SQL
I have the following dataset which I'm trying to create SQL to generate the FEE column.
CUSTOMER
YEAR
MONTH
REVENUE
FEE
NOTES
CUSTOMER A
FY24
1
0
0
CUSTOMER A
FY24
2
0
0
CUSTOMER A
FY24
3
0
0
...
- 113
0
votes
1
answer
120
views
How to get Future Grants over Schema in Snowflake?
I have a use case where I need to check for FUTURE grants on an input database and schema. If FUTURE grants are found and not match with Input Role then I want to revoke them and then assign the ...
- 2,447
0
votes
0
answers
27
views
Snowflake sproc for handling dynamic files
CREATE OR REPLACE PROCEDURE create_table_from_csv(file_name STRING, target_table STRING)
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
// Step 1: Create a temporary table to get the ...
- 1
3
votes
1
answer
139
views
Can we add additional columns to copyinto command while schema evolution is enabled
Is there any restrictions to add additional columns to copyinto command while schema evolution is enabled.
This is in addition to INCLUDE_METADATA columns as provided by snowflake i.e.,fields like ...
- 133
0
votes
0
answers
77
views
How to get the parent Query Id and root Id in Snowflake?
I have written a script that retrieves the status of all procedures, including any child procedures executed within a parent procedure. The script works correctly when all child procedures complete ...
- 2,447
-1
votes
1
answer
49
views
How to transpose these extra job_title rows into their own columns?
software im using is snowflake
I have a table of an employees job assignments with the company. They can hold multiple jobs actively at once (null in end_date). I'd like to consolidate this into 1 row:...
- 359
0
votes
2
answers
59
views
snowflake recursive query output
Here is my sample data table :
create or replace table test_case (
col_a string,
col_b string,
match_flag string);
insert into test_case
(col_a, col_b, match_flag)
values
('a', 'b', 'Y'),
('b', 'c', '...
- 127
1
vote
1
answer
38
views
Snowflake - Querying JSON
I need some help in getting this JSON file ingested in Snowflake so I can make queries on it:
{
"kind": "youtube#videoCategoryListResponse",
"etag": "...
1
vote
0
answers
140
views
Debezium Oracle connectors - how can I ignore/skip certain columns from the Oracle tables when creating Kafka messages
I have a set of tables in Oracle, each with the same columns.
I want to move this data from Oracle to Snowflake using Kafka streams.
One of the columns is a BLOB. I do not need this column in ...
0
votes
1
answer
60
views
Snowflake String Match
Column A
cat, dog, horse
monkey, donkey
cat, tree
I need a way to return me the rows which matches the string 'cat|ball|tree'. Here my expected output is TRUE for first and third row and false for ...
0
votes
0
answers
91
views
Unloading Snowflake table data from an EMPTY table into S3 in Parquet format
I am running copy jobs from snowflake tables to S3, and few of the tables are empty. Is there a way to generate an empty parquet file for these tables?
Below is the COPY command:
COPY INTO @...
0
votes
1
answer
50
views
Skipping Order if Older order is present
I need help with item # 3.
Skip duplicate orders
Skip cancelled orders
Skip order if an older order is present
In the given data, we need to:
skip id # 2,
skip id # 3 or 4.
skip id # 5 (as it ...
0
votes
2
answers
51
views
Insert row of default values into table
I need to insert just 1 row of default values into a Snowflake table. I know the following command works on other types of databases:
INSERT INTO my_table DEFAULT VALUES
Is there an equivalent in ...
- 5,946
1
vote
1
answer
156
views
Generating a single result set from multiple tables dynamically in Snowflake
I am trying to get a result set with 2 columns from snowflake using dynamic SQL, this is read from the INFORMATION_SCHEMA_TABLES metadata.
I simply want the (TABLE_NAME, PUB_DATE) columns for all ...
- 2,004
-1
votes
1
answer
271
views
how to get history of the snowflake table and how to get what is the version of the snowflake table and operation of the snowflake table as well
select max(timestamp) from (DESCRIBE HISTORY <tablename> ) where operation = 'MERGE'
DESCRIBE HISTORY <tablename> ).orderBy(col("version").desc).first.getLong(0)
I need to ...
0
votes
0
answers
28
views
Limit a user/role to not see the list of other users through snow sight - Users and Role section
Is there a way we can limit a user or a role to not see the list of other users through snow sight - Users and Role section.
Also, is there a way we can limit user to not have Public Role and only ...
0
votes
0
answers
128
views
Iterating through tables/columns in snowflake schema to find text value only working sometimes?
I am working within a snowflake DB schema that has about 200 tables and I need to figure out which table has a specific row-level value within a column. Looking for any table that contains a value = &...
- 359
0
votes
0
answers
74
views
unable to connect flyway to snowflake using user and key pair authentication
I'm trying to migrate DB objects(tables, views, procedures, etc) from dev to other regions using flyway. I'm currently using flyway 10.15.2 version and trying to connect to snowflake via key pair ...
-1
votes
1
answer
106
views
Group records on multiple columns in snowflake
Need to group records from following below sample input data on based on state,card number,card type, origin, method of payment, sales amount so that all records belonging to same group will be ...
- 23
0
votes
1
answer
56
views
Dynamically lateral flatten in Snowflake
{
"explosives_UG":{
"isCritical": false,
"value": "N/A"
},
"explosivesUG": {
"comment": "Test 619 313",
"...
- 67
0
votes
1
answer
38
views
Snowflake - Lateral flatten
{
"area": {
"id": "TEST1"
"value": "TEST1"
},
{ "testcom": {
"conditions": [
{
"assignedto&...
- 67
0
votes
0
answers
71
views
Snowflake Unsupported subquery type cannot be evaluated in UDF
I am trying to create a function that will take an h3id as input and returns the zip5 which the h3id belongs to, In order to do so I have created this UDF:
CREATE FUNCTION get_zip_from_h3(h3_id ...
- 45
1
vote
2
answers
1k
views
how to add Interval time in snowflake using a parameter
We have a use case where user will provide starttime, endtime, and Interval as parameters. and we need to pass these parameters to view logic where we have business logic, But the Interval function is ...
- 27
0
votes
0
answers
85
views
Issue in connecting to snowflake when implementing SchemChange CICD approach
I'm trying to implement the schemachnage cicd approach using github workflows to migrate to different environments (test/prd).
Below is my yaml file code. But it is not working as intended? I checked ...
- 21
0
votes
1
answer
477
views
How to programmatically select a specific schema in Snowpark within a Snowsight Python worksheet?
While writing the Snowpark code in a Snowsight Python worksheet, how can I select a specific schema? I know I can manually select it using the UI, but I want to select it programmatically.
import ...
- 33
0
votes
2
answers
788
views
Snowflake sequence issue
I'm trying to create a sequence into Snowflake for a table ID, I need it to increase 1 by 1 (1,2,3,4,...), how ever when I create it and try sequence.nextval it increases (1,101,201,301...) and I don'...
3
votes
1
answer
1k
views
Restore the data from the table recreated multiple times in snowflake
I had an issue when someone from my team recreated the table more than once using CREATE OR REPLACE as there were some new fields that had to be added. However, instead of adding a column using ALTER ...
- 1,087
0
votes
1
answer
48
views
how to test if row values in source when taken as columns in target
I have a source table like below,
ID
Column
value
1
apple
10
1
apple
8
1
banana
9
1
banana
12
I have a target table like below,
ID
apple
banana
1
10
9
1
8
12
How do I test/verify this using ...
- 79
0
votes
0
answers
39
views
Simplifying Eliminating Duplicate Columns SQL
I have the following code:
update tble
set
offer_id_02 = offer_id_03,
offer_id_03 = offer_id_04,
offer_id_04 = offer_id_05,
offer_id_05 = offer_id_06,
offer_id_06 = offer_id_07,
offer_id_07 = ...
- 139
2
votes
1
answer
2k
views
Alternative to st.file_uploader Snowflake Streamlit
I'm working on developing an app using Streamlit where users can upload a CSV file and insert it into a table or display its contents . I've attempted to use st.file_uploader, but it seems to be ...
- 1,191
0
votes
1
answer
496
views
Snowflake error : Numeric value '(501.00)' is not recognized
I have a large dataset, about 600 columns. There is a lot of casting happening to get things formatted properly. I am currently getting an error like this:
Numeric value '(501.00)' is not recognized.
...
- 31
1
vote
3
answers
78
views
SQL weekending date calculation
I have a set of data which has some dates of the week. I need to extract the next Friday date from the data and if the date is already on Friday then print the same date. I have tried multiple sources ...
- 11
0
votes
0
answers
39
views
What is the version compatibility of spring boot 2.6.0 and snowflake-jdbc driver?
I need to upgrade the snowflake jdbc driver in my spring boot 2.6.0 application. I not able to find which snowflake jdbc driver version is most compatible for spring boot 2.6.0 version .Any help or ...
- 97
0
votes
1
answer
345
views
How to use argument as a value inside snowflake SQL function?
I'm trying to create a SQL function like below, I was passing databasename, schemaname and table name as input. I was not able to use the input database name for the information_schema
below is my ...
- 21
0
votes
1
answer
231
views
Snowflake warehouse access issue inside stored procedure
I have written a stored procedure that runs multiple queries with select and show keywords. This stored procedure executes as the caller.
This is a sample query that I am running:
sq := 'SHOW FUTURE ...
- 2,447
0
votes
0
answers
21
views
SSRS toggle filter based on a parameter value
I am using Power Bi report builder that is running off a Power Bi data model so I am quite limited to putting all this fixes in a Stored Proc.
I have the following req :-
If @Param1 = Y then School no....
