从Snowflake的Temp Table读取数据到Jupyter Notebook

Question

我正在尝试从Snowflake查询数据到Jupyter Notebook。由于一些列没有出现在原始表中，所以我创建了一个临时表，其中包含所需的新列。不幸的是，由于工作限制，我不能在这里展示全部输出。但是当我运行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

临时表只在创建会话时存在:

临时表的Time Travel保留期为1天;但是，一旦会话(创建表的会话)结束，临时表就会被清除，因此实际保留期为24小时或会话的剩余时间，以较短的时间为准。

您可能需要使用暂态表来代替:

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