英文:
AWS Redshift Leader Node-Only Function with table reference ambiguity
问题
I understand your request to only translate the text and exclude the code. Here's the translation of the provided text:
我正在尝试在AWS Redshift上运行INFORMATION_SCHEMA的查询。如果我在一个数据库上运行查询，它完全正常，但当我尝试在不同的数据库上运行相同的查询时，会出现LEADER_NODE-仅函数错误。
请注意，这两个数据库都位于AWS上的同一服务器主机上。
错误：“ErrorCode=UserErrorOdbcOperationFailed，'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ERROR [0A000] [Microsoft][Amazon Redshift] (30)尝试执行查询时发生错误：[SQLState 0A000] ERROR：不支持指定的类型或函数（每个INFO消息一个） 在Redshift表上不支持。
错误[01000] INFO：不支持函数“has_table_privilege(oid,text)”。
错误[01000] INFO：不支持函数“has_column_privilege(oid,smallint,text)”。
“。
我理解存在LEADER-Node仅函数和Compute-Node仅函数的概念，但如果我指向同一服务器主机上的不同数据库，那么在information_schema上运行相同的查询是可以的。
我正在使用Azure Data Factory复制数据活动连接到Redshift，使用卸载功能。
英文:
I'm trying to run queries on INFORMATION_SCHEMA on AWS Redshift. My query works totally fine if i'm running on one database, But running into LEADER_NODE- functions only errors when i'm trying to run the same query on different database.
Please note that both the databases are on the same server host in AWS.
Error: "ErrorCode=UserErrorOdbcOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ERROR [0A000] [Microsoft][Amazon Redshift] (30) Error occurred while trying to execute a query: [SQLState 0A000] ERROR: Specified types or functions (one per INFO message) not supported on Redshift tables.
ERROR [01000] INFO: Function "has_table_privilege(oid,text)" not supported.
ERROR [01000] INFO: Function "has_column_privilege(oid,smallint,text)" not supported.
".
I understand that there is a concept of LEADER-Node only functions and Compute-Node only functions, but same query on information_schema works if i point to different database on the same server host.
I'm using Azure Data Factory copy data activity to connect to Redshift using unload functionality.
答案1
得分: 1
跨数据库查询不是你想象的那样。
"跨数据库查询并非通过改进系统表的使用来实现，以使Redshift现在可以访问跨数据库的表，而是通过从远程数据库复制查询的表到本地数据库，导致每个发出跨数据库查询的数据库中都有一个表的副本。如果远程表不在本地磁盘上，第一次查询会很慢，因为它必须等待复制表的本地副本，并且类似地，当对远程表进行更新后，更新后的下一次查询会很慢，因为它必须将更新内容带到表的本地副本。"
在进行跨数据库操作时，你需要复制远程表格，这涉及到工作节点，因此你的领导节点仅查询将会失败。
英文:
Cross-database queries are not what you think they are.
https://www.redshiftresearchproject.org/white_papers/downloads/cross_database_queries.html
"Cross-database queries are not implemented by improvements in the use of the system tables such that Redshift can now access tables across databases, but rather by bringing over from the remote database to the local database a copy of the table being queried, leading to a duplicate of the table in every database issuing cross-database queries to that table. Where the remote table is not on local disk, the first query is slow, as it must wait for a local copy of the table to be made and similarly, when updates are made to the remote table, the next query after the updates is slow, as it must bring the updates over to the local copy of the table."
Where you are going cross-database, you are bringing over a copy of the remote table, and this involves the worker nodes, and so your leader node only query fails.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论