aws redshift
This article gives you an overview of configuring the AWS Redshift cluster to use it from a locally installed IDE or tools.
本文為您概述了如何配置AWS Redshift叢集以通過本地安裝的IDE或工具使用它。
介紹 (Introduction)
In my last article, Getting started with AWS Redshift, we saw how to create an AWS Redshift cluster. Though Redshift provides an in-console browser-based query editor interface, often it’s not sufficient for the regular day to day database development needs. The query editor interface is generally used for a quick preview style of checks or a sneak peek into the Redshift database. The standard practice is that developers and administrators use a locally installed tool or IDE (Integrated Development Environment) of choice installed on a local machine or a virtual machine on the cloud, from which they connect to the Redshift cluster endpoint. These IDEs generally are packed with a lot of features that support day to day needs of a developer or administrator to perform various actions on the Redshift cluster. In this article, we will explore the step-by-step process to configure a Redshift cluster in a way that we can connect to it using IDEs of choice. The following steps will assume that you have a working Redshift cluster and have the required administrative rights to operate on the cluster.
在上一篇文章“ AWS Redshift入門”中 ，我們了解了如何建立一個AWS Redshift叢集。 盡管Redshift提供了基于控制台的基于浏覽器的查詢編輯器界面，但是對于正常的日常資料庫開發需求而言，這通常是不夠的。 查詢編輯器界面通常用于檢查的快速預覽樣式或對Redshift資料庫的預覽。 标準做法是，開發人員和管理者使用本地安裝的工具或選擇的IDE（內建開發環境）安裝在本地計算機或雲上的虛拟機上，并從中連接配接到Redshift叢集端點。 這些IDE通常包含許多功能，這些功能可滿足開發人員或管理者在Redshift叢集上執行各種操作的日常需求。 在本文中，我們将逐漸探索一種配置Redshift群集的分步過程，使我們可以使用所選的IDE将其連接配接。 以下步驟将假定您具有正常工作的Redshift叢集，并且具有在該叢集上運作所需的管理權限。
AWS Redshift IDE (AWS Redshift IDEs)
Several popular IDEs support working with Redshift like SQL Workbench, Aginity Pro for Redshift, Jetbrain’s DataGrip and many others. Redshift supports JDBC and ODBC interfaces as well as drivers using which one can connect to the redshift cluster. These drivers can be downloaded from here. If you are using an IDE that does not have out-of-box support for Redshift, you can install and configure these drivers in your IDE to work with Redshift. We would be using an IDE in this article that has out-of-box support for Redshift, so you would not need to perform this step of configuring the driver.
幾種流行的IDE支援與Redshift一起使用，例如SQL Workbench，用于Redshift的Aginity Pro，Jetbrain的DataGrip等。 Redshift支援JDBC和ODBC接口以及驅動程式，通過該接口可以連接配接到redshift叢集。 這些驅動程式可以從此處下載下傳。 如果您使用的IDE不具有對Redshift的現成支援，則可以在IDE中安裝和配置這些驅動程式以與Redshift一起使用。 我們将在本文中使用對Redshift具有開箱即用支援的IDE，是以您無需執行配置驅動程式的步驟。
Aginity Pro for Redshift is one of the most popular IDEs that is used with Redshift, which can be downloaded and used freely for individual use from here. Download and install this IDE if you are using a Windows or Mac-based workstation, as it does not support Linux.
用于Redshift的Aginity Pro是與Redshift一起使用的最受歡迎的IDE之一，可以從此處免費下載下傳和使用以供個人使用。 如果您使用的是基于Windows或Mac的工作站，請下載下傳并安裝此IDE，因為它不支援Linux。
For users who are using a Linux based workstation, they can use other alternate IDEs like SQL Workbench, which is also available for free individual use, and can be downloaded from here. Install the IDE of your choice, as we would be using this IDE to connect to the AWS Redshift cluster once we have configured the cluster endpoint.
對于使用基于Linux的工作站的使用者，他們可以使用其他替代IDE（例如SQL Workbench），也可以免費使用，可以從此處下載下傳。 安裝您選擇的IDE，因為一旦配置了叢集終端節點，我們将使用此IDE連接配接到AWS Redshift叢集。
AWS Redshift叢集終端節點 (AWS Redshift Cluster Endpoint)
Once the IDE is installed on the local machine, its time to configure the redshift cluster endpoint. Navigate to the Redshift dashboard, where you would find your cluster listed as shown below.
将IDE安裝在本地計算機上之後，就可以配置redshift叢集端點了。 導航到Redshift儀表闆，您将在其中找到列出的叢集，如下所示。
Open the Redshift cluster by clicking on the name of the cluster which will navigate you to the dashboard page of the selected cluster. Click on the Properties tab to explore the details of the cluster. On the left-hand side of the cluster, you would find the connection details like the cluster endpoint which is the address of the cluster and the JDBC connection string which you can use to connect to the cluster from the tools of your choice, as shown below.
通過單擊叢集名稱來打開Redshift叢集，該名稱将導航到所選叢集的儀表闆頁面。 單擊屬性頁籤以浏覽叢集的詳細資訊。 在叢集的左側，您會找到連接配接詳細資訊，例如叢集端點（它是叢集的位址）和JDBC連接配接字元串，您可以使用它們從所選工具連接配接到叢集，如圖所示下面。
AWS Redshift網絡配置 (AWS Redshift Network Configuration)
Scroll to the very bottom of the page and you would find a section titled Network and security. You would find the details like the VPC (Virtual Private Cloud) which is the network in which the redshift cluster is created, and the security group which contains the list of inbound and outbound rules to allow or deny traffic from and to the listed destinations. Click on the security group link, and that would navigate you to the security group page on the VPC service.
滾動到頁面的最底部，您将找到标題為網絡和安全性的部分。 您會發現詳細資訊，例如VPC（虛拟私有雲）（在其中建立了redshift叢集的網絡）以及安全組，該安全組包含允許或拒絕來自或到達所列目的地的流量的入站和出站規則清單。 單擊安全組連結，這會将您導航到VPC服務上的安全組頁面。
Once you are on the security group page, you would be able to see the details regarding inbound rules and outbound rules. We need to ensure that the security group allows incoming and outgoing traffic on the network in which the AWS Redshift cluster is hosted. If you are using the default security group and default VPC, the security group allows all outbound traffic, but allows inbound traffic only from the security group. We need to modify the inbound rule and add the rule to allow traffic on the port 5439 which is generally the default Redshift port. If you are using a non-default port, we need to open inbound traffic on that port.
進入安全組頁面後，您将能夠檢視有關入站規則和出站規則的詳細資訊。 我們需要確定安全組在托管AWS Redshift叢集的網絡上允許傳入和傳出流量。 如果使用預設安全組和預設VPC，則安全組允許所有出站流量，但僅允許來自安全組的入站流量。 我們需要修改入站規則并添加規則以允許端口5439上的流量通過，該端口通常是預設的Redshift端口。 如果您使用的是非預設端口，則需要在該端口上打開入站流量。
Click on the inbound rule, and you would be able to see the rules as shown below. Click on Add Rule button, select Redshift in the port type. This will auto-select the protocol and port range options. In the source section, to add your local machine IP, you can click on MyIP which will add your IP address. If you want to add a network range or multiple addresses, you can do the same as well by using the Custom option. After the required IP address on which the IDE is installed is added to the inbound security rule, save the rules by clicking on the Save button. This completes the network configuration required to allow traffic from the machine on which the IDE is installed to the AWS Redshift cluster.
單擊入站規則，您将能夠看到如下所示的規則。 單擊添加規則按鈕，在端口類型中選擇Redshift。 這将自動選擇協定和端口範圍選項。 在源部分中，要添加本地計算機IP，可以單擊MyIP，這将添加您的IP位址。 如果要添加網絡範圍或多個位址，也可以通過使用“自定義”選項執行相同的操作。 将安裝了IDE的所需IP位址添加到入站安全規則後，單擊“儲存”按鈕儲存規則。 這完成了允許從安裝IDE的計算機到AWS Redshift叢集的流量所需的網絡配置。
建立一個AWS Redshift連接配接 (Creating an AWS Redshift Connection)
Once the network configuration is completed, its time to make a connection to the Redshift cluster. It is assumed that Aginity Pro is installed, and this article will explain the steps to configure a connection using it. You can perform the same or similar steps on any other IDE as well that supports JDBC or Redshift in general.
網絡配置完成後，就可以連接配接到Redshift叢集了。 假定已安裝Aginity Pro，本文将介紹使用它配置連接配接的步驟。 您也可以在任何其他通常支援JDBC或Redshift的IDE上執行相同或相似的步驟。
Open Aginity Pro and click on the plus icon in the Connections tab as shown below. It comes with a readily configured Redshift JDBC driver, so you do not need to configure it. You would find Redshift as one of the options in the supported data sources list. Once your connection is created, it would look as shown on the left-most side of the connections tab.
打開Aginity Pro，然後在“ 連接配接”頁籤中單擊加号圖示，如下所示。 它帶有易于配置的Redshift JDBC驅動程式，是以您無需對其進行配置。 您會在支援的資料源清單中找到Redshift作為選項之一。 建立連接配接後，其外觀将顯示在“連接配接”頁籤的最左側。
Click on Redshift and that would open the connection details tab as shown below. Provide a relevant connection name in the Connection Name box. In the host section, provide the AWS Redshift cluster endpoint from the properties tab of the cluster, which we saw earlier. Leave the port with the default value, unless you are using a non-default port, in which case you should change the value of the port being used. Select the database name as dev or whatever is the database that you intend to connect. Provide the user id and password using which you intend to connect to the cluster.
單擊Redshift，這将打開連接配接詳細資訊頁籤，如下所示。 在“連接配接名稱”框中提供相關的連接配接名稱。 在主機部分中，從叢集的屬性頁籤中提供AWS Redshift叢集終端節點，我們之前已經看到過。 除非使用非預設端口，否則将端口保留為預設值，在這種情況下，應更改所使用端口的值。 選擇資料庫名稱為dev或要連接配接的資料庫名稱。 提供您打算用來連接配接叢集的使用者辨別和密碼。
After filling up the connection details, click on the Test button to test the connectivity between the IDE and the cluster. If the network connectivity is not configured correctly, the connection initiated from IDE will not be able to reach or access the cluster. This would result in an error. If the network does not allow the traffic from your machine to reach the AWS Redshift cluster, the testing will result in an error as shown below.
填寫完連接配接詳細資訊後，單擊“測試”按鈕以測試IDE和群集之間的連接配接。 如果未正确配置網絡連接配接，則從IDE啟動的連接配接将無法通路或通路群集。 這将導緻錯誤。 如果網絡不允許來自您計算機的流量到達AWS Redshift叢集，則測試将導緻錯誤，如下所示。
If the connectivity is successful, you would be shown a successful connection result as shown below. After the successful testing, you can be certain that the network connectivity between your machine and the cluster is working fine.
如果連接配接成功，将顯示如下所示的成功連接配接結果。 成功測試之後，可以确定計算機與群集之間的網絡連接配接正常。
After a successful connection, you can save the connection and return to the main query interface window. You would find the connection listed in the connections pane window. Upon expanding this, you would find all the database objects listed under the selected database and schema as shown below. You can execute SQL queries from the query interface on the right side and the output results of the query execution would appear at the bottom of the query window.
成功連接配接後，您可以儲存連接配接并傳回主查詢界面視窗。 您會在連接配接窗格視窗中找到列出的連接配接。 展開後，您将找到在所選資料庫和模式下列出的所有資料庫對象，如下所示。 您可以從右側的查詢界面執行SQL查詢，查詢執行的輸出結果将顯示在查詢視窗的底部。
The user and user group related details and other database objects which can be used for administrative purposes can be found listed under the security node as shown below.
可以在安全性節點下找到與使用者和使用者組相關的詳細資訊以及可用于管理目的的其他資料庫對象，如下所示。
Once the connection is successfully established from IDE to the AWS Redshift cluster endpoint, developers and administrators can conveniently access Redshift from their local machine without the need of logging on to AWS Console every time.
一旦成功建立了從IDE到AWS Redshift叢集終端節點的連接配接，開發人員和管理者就可以友善地從本地計算機通路Redshift，而無需每次都登入到AWS Console。
結論 (Conclusion)
In this article, we understood the process of installing IDEs on the local machine to conveniently access an AWS Redshift cluster. We configured the AWS network to allow traffic from the local machine to the Redshift cluster. And finally, after establishing a successful connection, we learned to open connections to the cluster from the IDE.
在本文中，我們了解了在本地計算機上安裝IDE以友善地通路AWS Redshift叢集的過程。 我們将AWS網絡配置為允許從本地計算機到Redshift叢集的流量。 最後，在建立成功的連接配接之後，我們學會了從IDE打開與叢集的連接配接。
翻譯自: https://www.sqlshack.com/access-aws-redshift-from-a-locally-installed-ide/
aws redshift