search

Snowflake-Labs / Excelerator

This is an Excel Addin for Windows that reads and writes data to Snowflake
Apache License 2.0
80 stars 22 forks source link

Error on SHOW GRANTS When Uploading Data to Snowflake #43

Closed SuccinctGasca closed 1 year ago

SuccinctGasca commented 1 year ago

Symptom: I am able to connect and get a list of databases, but when I attempt to upload a small table to Snowflake, I get the following error:

"SQL Compilation Error: Mismatch between the number of columns produced by "SHOWGRANTS" and the number of aliases specified." Excelerator Error

Last statement executed on Snowflake: with ShowGrants (created_on,privilege,granted_on,name,granted_to,grantee_name,grant_option,granted_by) as (select * from table(result_scan(last_query_id()))) select 'grant '|| privilege|| ' on table ' || '"DB_NAME"."DBO"."TEST2234100_BackupForExcel"' || ' to '|| granted_to ||' ' || grantee_name || IFF( grant_option='true' , ' with grant option' ,'' ) from ShowGrants where privilege <>'OWNERSHIP' order by created_on desc

System Info: Excelerator: The latest version on the Main branch as of Today Windows 10 Pro Version 10.0.19045 Microsoft® Excel® for Microsoft 365 MSO (Version 2306 Build 16.0.16529.20100) 64-bit

tecampbell commented 1 year ago

Same issue here. Is this issue being worked?

Thanks, Terry

tecampbell commented 1 year ago

Proposed fix -

Function grantAllPrivsToClonedTableSQL(origTable As String, clonedTable As String) Dim sql As String

Call Utils.execSQLFireAndForget("show grants on " & origTable)
sql = "with ShowGrants (created_on,privilege,granted_on,name,granted_to,grantee_name,grant_option,granted_by, granted_by_role_type) as " & _
"(select ""created_on"",""privilege"",""granted_on"",""name"",""granted_to"",""grantee_name"",""grant_option"",""granted_by"",""granted_by_role_type"" from  table(result_scan(last_query_id()))) " & _
"select 'grant '|| privilege|| ' on table ' || '" & clonedTable & "' || ' to '|| granted_to ||' ' || grantee_name || " & _
"IFF( grant_option='true' , ' with grant option' ,'' ) " & _
"from ShowGrants where privilege <>'OWNERSHIP' order by created_on desc"

grantAllPrivsToClonedTableSQL = Utils.execSQLReturnConcatResults(sql, "~")

End Function

miro-licek commented 1 year ago

@ssegal100 : after adding _granted_by_roletype into function grantAllPrivsToClonedTableSQL of Load script the issue is solved. If you grant me necessary permissions I can push new fix branch and create PR for your review.

ssegal100 commented 1 year ago

@miro-licek Thank you so much. How can I give you access?

pankaj-k commented 1 year ago

What changed at Snowflake level? I am also seeing this error.

tecampbell commented 1 year ago

The number of columns returned by the SHOW GRANTS call changed.

Proposed fix (I have updated my local copy and it works perfect) -

Function grantAllPrivsToClonedTableSQL(origTable As String, clonedTable As String) Dim sql As String

Call Utils.execSQLFireAndForget("show grants on " & origTable) sql = "with ShowGrants (created_on,privilege,granted_on,name,granted_to,grantee_name,grant_option,granted_by, granted_by_roletype) as " & "(select ""created_on"",""privilege"",""granted_on"",""name"",""granted_to"",""grantee_name"",""grant_option"",""granted_by"",""granted_by_role_type"" from table(result_scan(last_queryid()))) " & "select 'grant '|| privilege|| ' on table ' || '" & clonedTable & "' || ' to '|| granted_to ||' ' || granteename || " & "IFF( grantoption='true' , ' with grant option' ,'' ) " & "from ShowGrants where privilege <>'OWNERSHIP' order by created_on desc"

grantAllPrivsToClonedTableSQL = Utils.execSQLReturnConcatResults(sql, "~") End Function

cschlagel commented 1 year ago

How do we implement this fix on our own copy @tecampbell? Can you give me steps to add this code above?

pankaj-k commented 1 year ago

Can we expect a patch soon?

SuccinctGasca commented 1 year ago

It's a great tool. Would be awesome if the creators are still maintaining it! Fingers crossed

tecampbell commented 1 year ago

a

Open a spreadsheet. Click Developer and open the VB Modules. Search for the function grantAllPrivsToClonedTableSQL nd replace it with this code -

Function grantAllPrivsToClonedTableSQL(origTable As String, clonedTable As String) Dim sql As String

Call Utils.execSQLFireAndForget("show grants on " & origTable) sql = "with ShowGrants (created_on,privilege,granted_on,name,granted_to,grantee_name,grant_option,granted_by, granted_by_roletype) as " & "(select ""created_on"",""privilege"",""granted_on"",""name"",""granted_to"",""grantee_name"",""grant_option"",""granted_by"",""granted_by_role_type"" from table(result_scan(last_queryid()))) " & "select 'grant '|| privilege|| ' on table ' || '" & clonedTable & "' || ' to '|| granted_to ||' ' || granteename || " & "IFF( grantoption='true' , ' with grant option' ,'' ) " & "from ShowGrants where privilege <>'OWNERSHIP' order by created_on desc"

grantAllPrivsToClonedTableSQL = Utils.execSQLReturnConcatResults(sql, "~") End Function

Click Save.

ssegal100 commented 1 year ago

This happened because there was a new column added to ShowGrants. My design of using 'Select *' was a very bad choice and caused this issue. As you see, the above solution calls out the needed columns explicitly, a much better durable design. I'll get a patch out very soon but in the mean time you can update your copy by following the steps above. Steve

pankaj-k commented 1 year ago

Thanks @ssegal100. Awaiting the updates.

ssegal100 commented 1 year ago

@pankaj-k Would you test the file SnowflakeExcelAddinWithGrantFix.xlam? I've made the change. If it works for you I'll replace the original. I'm unable to test due to an issue with my laptop. Thanks.

pankaj-k commented 1 year ago

@ssegal100 Just tested it. It worked on my PC.

While you are at it can you also fix a typo in file "src/UploadDataForm.frm"

vbNewLine & "Continue uplading?", vbOKCancel, "Update Conflict") = vbCancel Then

ssegal100 commented 1 year ago

@pankaj-k I made that typo change and pushed the new Addins. Would someone test the new Addin and then we can close this issue.

Thanks for you help everyone. Steve

Francis-Beaulieu commented 1 year ago

I am pleased to formally confirm that the issue has been resolved

tecampbell commented 1 year ago

[like] Terrance Campbell reacted to your message:

From: Francis-Beaulieu @.> Sent: Thursday, July 27, 2023 6:05:54 PM To: Snowflake-Labs/Excelerator @.> Cc: Terrance Campbell @.>; Mention @.> Subject: [EXTERNAL] Re: [Snowflake-Labs/Excelerator] Error on SHOW GRANTS When Uploading Data to Snowflake (Issue #43)

This email originated outside of PODS. Please verify sender before clicking links or opening attachments!

I am pleased to formally confirm that the issue has been resolved

— Reply to this email directly, view it on GitHubhttps://protect-us.mimecast.com/s/O3CVC9rLVYuMQAZmsogsti?domain=github.com, or unsubscribehttps://protect-us.mimecast.com/s/0AO3C0RXJDhMVQE2sDyN9O?domain=github.com. You are receiving this because you were mentioned.Message ID: @.***>

ssegal100 commented 1 year ago

Thanks everyone, Steve

tecampbell commented 1 year ago

[like] Terrance Campbell reacted to your message:

From: Steven Segal @.> Sent: Thursday, July 27, 2023 6:32:22 PM To: Snowflake-Labs/Excelerator @.> Cc: Terrance Campbell @.>; Mention @.> Subject: [EXTERNAL] Re: [Snowflake-Labs/Excelerator] Error on SHOW GRANTS When Uploading Data to Snowflake (Issue #43)

This email originated outside of PODS. Please verify sender before clicking links or opening attachments!

Closed #43https://protect-us.mimecast.com/s/4Ja_C68zPKT1z7K2u6-lUg?domain=github.com as completed.

— Reply to this email directly, view it on GitHubhttps://protect-us.mimecast.com/s/Dl6hC5y1OKSxE3GmuzGGJD?domain=github.com, or unsubscribehttps://protect-us.mimecast.com/s/ZHlyC73WQVHQl2k5uBjVT4?domain=github.com. You are receiving this because you were mentioned.Message ID: @.***>