从Snowflake中的临时表读取数据到Jupyter笔记本

Question

我正试图将雪花的数据查询到Jupyter笔记本中。由于原始表中不存在某些列，因此我创建了一个临时表，其中包含所需的新列。不幸的是，由于工作限制，我无法在这里显示整个输出。但是当我运行CREATE TEMPORARY TABLE命令时，得到了以下输出

Table CUSTOMER_ACCOUNT_NEW successfully created.

下面是我用来生成TEMP表的查询

CREATE OR REPLACE TEMPORARY TABLE DATA_LAKE.CUSTOMER.CUSTOMER_ACCOUNT_NEW AS
SELECT ID,
   VERIFICATION_PROFILE,
   get_path(VERIFICATION_PROFILE,'identityMindMostRecentResults')::VARCHAR AS identitymind,
   get_path(VERIFICATION_PROFILE,'identityMindMostRecentResults."mm:1"')::VARCHAR AS mm1,
   get_path(VERIFICATION_PROFILE,'identityMindMostRecentResults."mm:2"')::VARCHAR AS mm2,
   get_path(VERIFICATION_PROFILE,'identityMindMostRecentResults.res')::VARCHAR AS res,
   get_path(VERIFICATION_PROFILE,'identityMindMostRecentResults."ss:1"')::VARCHAR AS sanctions,
   get_path(VERIFICATION_PROFILE,'autoVerified.facts.account.riskScore')::VARCHAR AS riskscore,
   get_path(VERIFICATION_PROFILE,'autoVerified.facts.giact.verificationResponse')::VARCHAR AS GIACT,
   get_path(VERIFICATION_PROFILE,'autoVerified.facts.account.type')::VARCHAR AS acct_type,
   get_path(VERIFICATION_PROFILE,'autoVerified.verified')::VARCHAR AS verified,
   get_path(VERIFICATION_PROFILE,'bankInformationProvided')::VARCHAR AS Bank_info_given,
   get_path(VERIFICATION_PROFILE,'businessInformationProvided')::VARCHAR AS Business_info_given,
   get_path(VERIFICATION_PROFILE,'autoVerified.facts.account.industry.riskLevel')::VARCHAR AS industry_risk
FROM DATA_LAKE.CUSTOMER.CUSTOMER_ACCOUNT
WHERE DATEDIFF('day',TO_DATE(TIME_UPDATED),CURRENT_DATE())<=90

我想提到VERIFICATION_PROFILE是一个JSON blob，因此我必须使用get_path来检索值。此外，像mm:1这样的键最初是双引号的，所以我使用了它，它在snowflake中工作得很好

然后使用雪花连接器python，我尝试运行以下查询

 import pandas as pd
 import warnings
 import snowflake.connector as sf

 ctx = sf.connect(
 user='*****',
  password='*****',
 account='*******',
 warehouse='********',
 database='DATA_LAKE',
 schema='CUSTOMER'
)
 #create cursor
curs = ctx.cursor()


sqlnew2 = "SELECT * \
FROM DATA_LAKE.CUSTOMER.CUSTOMER_ACCOUNT_NEW;"
curs.execute(sqlnew2)
df = curs.fetch_pandas_all()

这里curs是前面创建的游标对象。然后我得到了以下信息

ProgrammingError: 002003 (42S02): SQL compilation error:
Object 'DATA_LAKE.CUSTOMER.CUSTOMER_ACCOUNT_NEW' does not exist or not authorized.

请问雪花连接器是否允许我们从临时表查询数据？非常感谢您的帮助/建议

Answer 1

临时表仅在创建它们的会话存在时有效：

Temporary tables can have a Time Travel retention period of 1 day; however, a temporary table is purged once the session (in which the table was created) ends so the actual retention period is for 24 hours or the remainder of the session, whichever is shorter.

您可能希望改用临时表：

https://docs.snowflake.com/en/user-guide/tables-temp-transient.html#comparison-of-table-types