如何在没有权限配置外部表的情况下联接多个Azure数据库? [英] How to join multiple azure databases without rights to configure external tables?

问题描述

在当前设置中，我使用Authentication = Active Directory-Integrated连接到Azure SQL Server.这种访问方法仅允许一次访问单个数据库.该架构是从内部SQL Server环境迁移而来的，并进行了更改，以使云开发变得可行，但是仍然必须在数据库之间进行分析和调试.通常，只需使用旧版SQL Server配置进行跨数据库联接，如果数据库位于单独的服务器上，则可能涉及链接服务器.在云或蔚蓝中，您应使用弹性搜索或避免需要这样做的体系结构设计.由于您需要更改任何外部数据源权限，所以我想我可能不得不借助ETL工具来执行这种类型的联接，但我想知道是否还有其他选项或工具可以允许这种联接基本上只能通过读取访问权限在azure中执行跨数据库或跨服务器联接.

In my current setup I connect to an Azure SQL Server using Authentication=Active Directory - Integrated. This method of access only allows access to a single database at a time. The architecture was migrated from an on premises SQL server environment with changes to make cloud development feasible, but still analytics and debugging must occur across databases. Typically one would simply do a cross database join with a legacy SQL Server configuration, possibly involving link servers if the databases were on separate servers. In the cloud or azure you are supposed to use Elastic search or avoid the architecture designs that make this needed. On the source data I only have read access which is a problem since you need ALTER ANY EXTERNAL DATA SOURCE permission so I am thinking I may have to resort to an ETL tool to perform this type of join, but wondering if there are other options or tools that allow one to essentially perform cross database or cross server joins in azure with only read access.

仅授予读取权限，执行这些类型的联接有什么想法?

What are some ideas for performing these types of joins given only read access?

推荐答案

我希望一个专家能回答这个问题，并告诉我我很无聊，但是这是我目前的想法，也许这会让她离开...我在下面看到的那条前进的路.

I was hoping a savant would answer this one and tell me I was dumb for asking, but here are my current thoughts maybe this will bring her out... path forward as I see it below.

只提供读访问权限，最好是一开始就将所有数据从不同来源拉到我具有完全开发权限的环境中(例如，如果本地主机较小，则为本地主机上的PostgreSQL或MySQL服务器...或者可能是单个开发位置确保企业拥有完整的开发权).在ETL工具(即SSIS)中创建此文件，然后运行，因为源中任何增量都需要更新的数据.随后，对该合并数据执行联接.

Given only read access it is best to pull all data at the onset from the different sources to an environment that I have full development rights (i.e. a PostgreSQL or MySQL server on localhost if small... or potentially a single development place on azure that the enterprise allows for full development rights). Create this in an ETL tool, i.e. SSIS, and run as updated data is needed for any delta in the source. Subsequently perform joins on this consolidated data.

这是有道理的，因为我需要对联接进行试验，处理足够小的数据或可传输的数据，因此重复数据不是资源浪费，并且可以直接在SQL编辑器中执行试验性联接，而不是在SQL编辑器中进行一个ETL工具.

This makes sense since I need to experiment with the joins, dealing with a small enough or transferable amount of data that the duplication of the data is not a resource hog, and would perform the experimental joins directly in a SQL editor instead of an ETL tool.

如果我有一个更大的数据集，我将尝试进行抽样，执行相同的练习，然后一旦理解了跨服务器联接，就可以在ETL工具中大规模执行它，这可能是一种理想的批处理方式最终并行执行.

If I had a much larger dataset I would attempt to sample down, perform the same exercise and then once the cross server join was understood execute it at scale in an ETL tool, probably in some sort of batch fashion that ideally could be eventually executed in parallel.

这篇关于如何在没有权限配置外部表的情况下联接多个Azure数据库?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！