Dbeaver Google Cloud Sql

admin



All DBeaver products are licensed but with different types of licenses. Commercial: Commercial: Commercial: Open-source, Apache 2.0: SQL databases support Support for relational SQL databases such as MySQL, PostgreSQL, Oracle, etc. NoSQL/BigData databases support Support for BigData/NoSQL databases such as MongoDB, Cassandra, Redis, InfluxDB. DBeaver is one of the best free SQL IDE’s I’ve seen. It has a great UI, it’s fast, and the Projects feature is a helpful way of organising your files and related information. If you’re considering using DBeaver for your SQL work, you can’t go wrong with this choice. Cloud SQL Proxy client: a command-line tool that establishes the proxy connection and provides secure access to your instances. When the Cloud SQL Proxy client runs, you do not need to add your IP to authorized networks or configure SSL. You can read more about the Cloud SQL Proxy in this article at cloud.google.com. The connection is secured.

Within the framework of this topic, we are going to use a PostgreSQL instance on the Google Cloud Platform. But you can use the same instructions to connect to MySQL and Microsoft SQL Server instances of Google Cloud SQL.

If you have not created the instance yet, see Creating instances at cloud.google.com.

From DataGrip, you can connect to your instances by using the following options.

  • Cloud SQL Proxy client: a command-line tool that establishes the proxy connection and provides secure access to your instances. When the Cloud SQL Proxy client runs, you do not need to add your IP to authorized networks or configure SSL. You can read more about the Cloud SQL Proxy in this article at cloud.google.com.

    The connection is secured. To connect to your instance, you need Google Cloud SDK and the Cloud SQL Proxy client.

  • Cloud SQL Connector for Java: libraries for JDBC drivers by using which you can connect securely to Cloud SQL databases. These libraries are available on the Release page of the cloud-sql-jdbc-socket-factory repository at github.com.

    The connection is secured. To connect to your instance, you need Google Cloud SDK and the JAR library file of the Cloud SQL Connector for Java.

  • Adding your network to authorized networks: adding your network to the list of authorized sources in the Google Cloud Console. After this procedure, you can connect to the PostgreSQL instance from your network by using the IP address of your instance.

    The connection is not secured (no encryption). To connect to your instance, you need to add your IP address or a range of IP addresses to authorized networks.

Dbeaver google cloud sql database

Configuring Google Cloud SDK

This configuration procedure is common for the Cloud SQL Proxy client and the Cloud SQL Connector for Java. If you plan to add your network to the list of authorized networks, you can skip this part.

To complete the connection's configuration, you need Google Cloud SDK.

Google Cloud SDK includes the gcloud utility that manages authentication and interactions with the Google Cloud Platform API. For more information about gcloud, see the gcloud reference at cloud.google.com.

Step 1. Installing Google Cloud SDK

For more information about the installation process, see the Installing Google Cloud SDK guide at cloud.google.com. This procedure provides a summary of steps from the guide.

  1. Download and extract the Google Cloud SDK archive.

  2. Run the gcloud init command to initialize the SDK. Follow the installation wizard in the command prompt.

Step 2. Register your application for the Cloud SQL Admin API

  1. Click the Enable the API button in the Enable the Cloud SQL Admin API section.

  2. Select your project in the drop-down list and click Continue.

    You must enable the Cloud SQL Admin API for each database project if you want to connect to the database in this project. For example, one project uses PostgreSQL, another project uses MySQL. You need to enable the API for both projects.

Step 3. Configure authentication

  1. Run the following command to authenticate the gcloud tool.

    gcloud auth login

    This command obtains your credentials and stores them in ~/.config/gcloud/. Now you can run gcloud commands from your terminal, and it will find your credentials automatically.

  2. Run the following command to obtain credentials for the Application Default Credentials (ADC) library.

    This command obtains your credentials for the ADC library. Now, any code or SDK that is running on your computer will be able to find the credentials automatically.

Configuring connections with the Cloud SQL Proxy client

Ensure that you have correctly configured Google Cloud SDK: initialized the gcloud tool; enabled the Cloud SQL Admin API; authenticated the gcloud tool; acquired credentials for the ADC library.

The Cloud SQL Proxy client communicates with your database through a secure tunnel. Read more about the client in About the Cloud SQL Proxy at cloud.google.com.

Step 1. Run the Cloud SQL Proxy client

  1. Download the Cloud SQL Proxy client. See installation instructions in Install the Cloud SQL Proxy client on your local machine at cloud.google.com.

  2. Start the proxy in the command prompt.

    ./cloud_sql_proxy -instances=connection_name=tcp:3306

    Replace the connection_name with the connection name of your instance. You can find this information on the Overview page of your instance. If the 3306 port is already in use, change it to another port (for example, 5435).

Step 2. Create a data source

  1. Ensure that the Cloud SQL Proxy client is running (see Step 1 ).

  2. Open data source properties. You can open data source properties by using one of the following options:

    • Navigate to File | Data Sources.

    • Press Ctrl+Alt+Shift+S.

    • In the Database tool window (View | Tool Windows | Database ), click the Data Source Properties icon .

  3. In the Data Sources and Drivers dialog, click the Add icon () and select your database vendor (in our case, PostgreSQL ).

  4. At the bottom of the data source settings area, click the Download missing driver files link. As you click this link, DataGrip downloads drivers that are required to interact with a database. To decrease the size of the installation package and keep driver versions up-to-date, drivers are not bundled with the IDE.

    You can specify your own drivers for the data source if you do not want to download the provided drivers. For more information about creating a database connection with your driver, see Add a user driver to an existing connection.

  5. From the Authentication list, select User & Password.

  6. Specify connection details.

    • Host: 127.0.0.1. You are connecting to a local machine because you use the Cloud SQL Proxy client.

    • Port: 3306, or any other port that you selected for the proxy (for example, 5435 ).

    • User: a user who has permission to access the database.

    • Password: password of a user.

    • Database: a database to which you want to connect.

  7. To ensure that the connection to the data source is successful, click the Test Connection link.

Configuring connections with the Cloud SQL Connector for Java

Ensure that you have correctly configured Google Cloud SDK: initialized the gcloud tool; enabled the Cloud SQL Admin API; authenticated the gcloud tool; acquired credentials for the ADC library.

The Cloud SQL Connector for Java is a set of libraries for MySQL, Microsoft SQL Server, and PostgreSQL JDBC drivers by using which you can connect to a Cloud SQL database without adding your IP to the list of authorized networks or SSL certificates. Libraries of the Cloud SQL Connector for Java are distributed as a source code on github.com. You can build this source code by using Maven or download the built JAR file from the Release page.

To build all libraries with Maven, clone the cloud-sql-jdbc-socket-factory repository, and run the following command from the cloned repository directory.

Dbeaver Google Cloud Sql Server

mvn -P jar-with-dependencies clean package -DskipTests

This connection type uses application default credentials and the Cloud SQL Connector for Java. Ensure that you obtained credentials for the Application Default Credentials (ADC) library. For more information about the ADC library authentication, see Step 3. Configure authentication.

Step 1. Add the Cloud SQL Connector to the driver

  1. Download the necessary JAR file from the Release page of the cloud-sql-jdbc-socket-factory repository at github.com.

    • PostgreSQL: postgres-socket-factory-<version>-jar-with-dependencies.jar

    • MySQL: mysql-socket-factory-<version>-jar-with-dependencies.jar

    • Microsoft SQL Server: cloud-sql-connector-jdbc-sqlserver-<version>-jar-with-dependencies.jar

  2. Open data source properties. You can open data source properties by using one of the following options:

    • Navigate to File | Data Sources.

    • Press Ctrl+Alt+Shift+S.

    • In the Database tool window (View | Tool Windows | Database ), click the Data Source Properties icon .

  3. In the Data Sources and Drivers dialog, click the Drivers tab.

  4. Click the driver of your database vendor and select Duplicate. Alternatively, press Ctrl+D.

  5. In the Name field, type a new driver's name.

  6. Click the Advanced tab and set the following properties:

    • PostgreSQL:

      socketFactory = com.google.cloud.sql.postgres.SocketFactory

    • MySQL:

      socketFactory = com.google.cloud.sql.postgres.SocketFactory

    • Microsoft SQL Server:

      socketFactoryClass = com.google.cloud.sql.sqlserver.SocketFactorsocketFactoryConstructorArg = <connection_name>

      , where <connection_name> is a connection name of your instance in Cloud SQL (for example, sql-server-307:europe-west3:db ). You can find this information on the Overview page of your instance.

  7. On the General tab, click the Add icon () and select Custom JARs.

  8. Navigate to the built or downloaded JAR library file (in our case, postgres-socket-factory-1.2.0-jar-with-dependencies.jar ).

  9. From the Class list, select the appropriate driver class (for example, org.postgresql.Driver ).

  10. Click Apply.

Sql

Step 2. Create a data source

  1. Open data source properties. You can open data source properties by using one of the following options:

    • Navigate to File | Data Sources.

    • Press Ctrl+Alt+Shift+S.

    • In the Database tool window (View | Tool Windows | Database ), click the Data Source Properties icon .

  2. In the Data Sources and Drivers dialog, click the Drivers tab, select the driver that you prepared on Step 1, and click the Create Data Source link.

    Alternatively, click the Add icon () and select the driver that you prepared on Step 1.

  3. In the Connection type list, select URL only.

  4. In the URL field, paste the URL in the following format:

    • PostgreSQL:

      jdbc:postgresql:///<DATABASE_NAME>?cloudSqlInstance=<INSTANCE_CONNECTION_NAME>&user=<USER_NAME>&password=<USER_PASSWORD>

    • MySQL:

      jdbc:mysql:///<DATABASE_NAME>?cloudSqlInstance=<INSTANCE_CONNECTION_NAME>&user=<USER_NAME>&password=<USER_PASSWORD>

    • Microsoft SQL Server:

      jdbc:sqlserver://;databaseName=<DATABASE_NAME>;user=<USER_NAME>;password=<USER_PASSWORD>

    , where

    • DATABASE_NAME: the database name to which you want to connect.

    • INSTANCE_CONNECTION_NAME: the instance connection name. You can find this information on the Overview page of your instance.

    • USER_NAME: a username that has access to the database.

    • USER_PASSWORD: a user's password.

    Note that you do not need to specify the host. Due to the properties that you set for the driver, the IP address of your instance will be detected automatically.

    Your URL might look something like this:

    • PostgreSQL:

      jdbc:postgresql:///guest?cloudSqlInstance=crucial-study-301709:us-central1:postgres&user=guest&password=guest

    • MySQL:

      jdbc:mysql:///guest?cloudSqlInstance=mysql-304410:europe-west3:mysql&user=guest&password=guest

    • Microsoft SQL Server:

      jdbc:sqlserver://;databaseName=guest;user=sqlserver;password=guest

Adding your network to authorized networks

This type of connection is unencrypted. To secure your connection, consider authorizing with SSL/TLS certificates.

To complete this part of the guide, you need to know your external IP address or a range of IP addresses. Use the CIDR notation (for example, 192.168.100.14/24 ).

Step 1. Add your network to authorized networks

  1. Open a page of your Cloud SQL Instance.

  2. Select the Connections tab and click Add network.

  3. In the Network field, type your external IP address or a range of IP addresses in the CIDR notation.

  4. Click Done and then Save.

    Wait until your instance is updated.

Step 2. Create a data source

  1. Open data source properties. You can open data source properties by using one of the following options:

    • Navigate to File | Data Sources.

    • Press Ctrl+Alt+Shift+S.

    • In the Database tool window (View | Tool Windows | Database ), click the Data Source Properties icon .

  2. In the Data Sources and Drivers dialog, click the Add icon () and select your database vendor (for example, PostgreSQL ).

  3. At the bottom of the data source settings area, click the Download missing driver files link. As you click this link, DataGrip downloads drivers that are required to interact with a database. To decrease the size of the installation package and keep driver versions up-to-date, drivers are not bundled with the IDE.

    You can specify your own drivers for the data source if you do not want to download the provided drivers. For more information about creating a database connection with your driver, see Add a user driver to an existing connection.

  4. In the Host field, type the IP address of your PostgreSQL instance. You can find this IP address on the overview page of your instance.

  5. From the Authentication list, select User & Password.

  6. In the User field, type the username that has access to the database.

  7. In the Password field, type the user's password.

  8. In the Database field, type the database name to which you want to connect.

  9. To ensure that the connection to the data source is successful, click the Test Connection link.

The CData JDBC Driver for Google Sheets implements JDBC standards that enable third-party tools to interoperate, from wizards in IDEs to business intelligence tools. This article shows how to connect to Google Sheets data with wizards in DBeaver and browse data in the DBeaver GUI.

Create a JDBC Data Source for Google Sheets Data

Follow the steps below to load the driver JAR in DBeaver.

Dbeaver Google Cloud Sql Client

  1. Open the DBeaver application and, in the Databases menu, select the Driver Manager option. Click New to open the Create New Driver form.
  2. In the Driver Name box, enter a user-friendly name for the driver.
  3. To add the .jar, click Add File.
  4. In the create new driver dialog that appears, select the cdata.jdbc.googlesheets.jar file, located in the lib subfolder of the installation directory.
  5. Click the Find Class button and select the GoogleSheetsDriver class from the results. This will automatically fill the Class Name field at the top of the form. The class name for the driver is cdata.jdbc.googlesheets.GoogleSheetsDriver.
  6. Add jdbc:googlesheets: in the URL Template field.

Dbeaver Google Cloud Sql Login

Dbeaver Google Cloud Sql

Dbeaver Google Cloud Sql Database

Create a Connection to Google Sheets Data

Follow the steps below to add credentials and other required connection properties.

  1. In the Databases menu, click New Connection.
  2. In the Create new connection wizard that results, select the driver.
  3. On the next page of the wizard, click the driver properties tab.

  4. Enter values for authentication credentials and other properties required to connect to Google Sheets.

    You can connect to a spreadsheet by providing authentication to Google and then setting the Spreadsheet connection property to the name or feed link of the spreadsheet. If you want to view a list of information about the spreadsheets in your Google Drive, execute a query to the Spreadsheets view after you authenticate.

    ClientLogin (username/password authentication) has been officially deprecated since April 20, 2012 and is now no longer available. Instead, use the OAuth 2.0 authentication standard. To access Google APIs on behalf on individual users, you can use the embedded credentials or you can register your own OAuth app.

    OAuth also enables you to use a service account to connect on behalf of users in a Google Apps domain. To authenticate with a service account, you will need to register an application to obtain the OAuth JWT values.

    See the Getting Started chapter in the help documentation to connect to Google Sheets from different types of accounts: Google accounts, Google Apps accounts, and accounts using two-step verification.

    Built-in Connection String Designer

    For assistance in constructing the JDBC URL, use the connection string designer built into the Google Sheets JDBC Driver. Either double-click the JAR file or execute the jar file from the command-line.

    java -jar cdata.jdbc.googlesheets.jar

    Fill in the connection properties and copy the connection string to the clipboard.

    Below is a typical connection string:

    jdbc:googlesheets:Spreadsheet=MySheet;InitiateOAuth=GETANDREFRESH

Query Google Sheets Data

You can now query information from the tables exposed by the connection: Right-click a Table and then click Edit Table. The data is available on the Data tab.