如何通过DataGrip或其他方式同时查询Amazon Redshift与本地数据源？

Hey there! Let's break down how to handle cross-source queries between Amazon Redshift and your local data in DataGrip— I’ve dealt with this exact scenario before, so here’s what you need to know:

关于OPENQUERY的支持情况 #

First off, OPENQUERY is a function specific to databases like SQL Server, and Amazon Redshift doesn’t natively support it for connecting to local external data sources. Even with DataGrip’s robust features, it can’t add functionality that Redshift itself doesn’t have built-in.

DataGrip自带的跨数据源查询方案（推荐） #

DataGrip has a handy built-in feature for cross-database/cross-source queries that doesn’t rely on the database’s own external connection capabilities. Here’s how to use it:

• Make sure you’ve already set up and tested connections for both your Redshift cluster and local data source (whether it’s a local DB like PostgreSQL/MySQL, or even a file-based source) in DataGrip.
• Open a new unbound query window (don’t open it from a specific connection’s console— use the global "New Query" option instead).
• Reference tables from different sources using the format [Data Source Name].[Schema].[Table Name]. For example:

  SELECT 
      rs.order_id,
      rs.customer_name,
      local.user_preferences.favorite_category
  FROM 
      "My-Redshift-Cluster".public.customer_orders rs
  LEFT JOIN 
      "Local-MySQL".app.user_preferences local
      ON rs.customer_id = local.customer_id
  WHERE 
      rs.order_date >= '2024-01-01'
  

  Just wrap your data source names in quotes if they have spaces or special characters. DataGrip will handle the rest, pulling data from both sources and joining it for you.

其他实用替代方案 #

• If your local data is in a file (CSV, Excel, etc.), you can directly import it as a temporary table in DataGrip: right-click the file, select "Import as Table", and then query it just like any other table alongside your Redshift data.
• For larger datasets or frequent queries, you could sync your local data to Redshift first— either by uploading it to S3 and creating an external table, or importing it into a Redshift temporary table. This lets you run all your queries directly within Redshift, which might be faster for big data volumes.

Hope this helps you get those cross-source queries up and running smoothly!

内容的提问来源于stack exchange，提问作者Andres Urrego Angel