CData Cloud Hub



Overview

The CData Cloud Hub allows you to access 100+ data sources in a consistent, standards-based manner. The Hub represents all sources as MySQL databases that can be queried using any MySQL client. The metadata for all data sources, access control, etc. is surfaced through MySQL primitives. Future versions will surface other standards-based mechanisms to access this data: TDS (SQL Server-based interface), OData, and PostgreSQL.

Getting Started

The following sections show your initial steps to logging into CData Cloud Hub for the first time. You will be provided with an admin user account and password using which you will manage other users and create databases that map to any of the supported sources.

Logging In

With your admin user, you can connect with any MySQL client, for example, with MySQL Workbench or the mysql command-line client, as shown below:

mysql --user admin --password --host myinstance.cdatacloud.net --port 3306

You should then change the default password to something more secure. This is done using the ALTER USER command as shown below.

ALTER USER 'admin'@'%' IDENTIFIED BY 'MyNewPass';

Using the Admin

The admin user has all permissions at both the system (global) and database (table) levels. It is better to restrict use of the admin to managing user accounts and databases; for general queries, create users and roles with the minimum permissions needed.

Managing Users and Databases

  • By creating data sources as the admin, you can create data sources that can be shared by multiple users.
  • By delegating create database permissions to a user, the admin allows users to provide their own connection strings and connect to their own accounts, as well as create databases that multiple users can access.

Creating a Database for a Source

The following sections cover adding data sources to CData Cloud Hub. Use the CREATE DATABASE statement to create a virtual database that wraps data access and access control to a data source in a standard MySQL database.

Creating a MySQL Database

To create the virtual database, you need to specify which data source you want to connect to, provide authentication properties, and configure any other necessary connection properties. You can do so with the CREATE DATABASE statement. This statement uses the following arguments:

  • name: The name of the MySQL database. You can choose any name you like. This is the database name your users will use to connect.
  • driver: The name of the underlying CData driver providing connectivity to the data source. Refer to the list of supported databases.
  • dburl: The connection properties needed to connect to the data source specified as a semi-colon separated list. Refer to the data source documentation for available properties and their description.
  • version (optional): The version of the driver. Skip this to use the latest version available alternatively set it to the version of your choice.

The following statement creates a virtual database using a typical connection string for a Salesforce connection:

CREATE DATABASE IF NOT EXISTS 'salesforcedb'
  DRIVER = 'Salesforce',
  DBURL = 'User=myUser;Password=myPassword;Security Token=myToken;'

Managing Users and Permissions

The admin user can manage access to data sources using MySQL's role-based access control system.

Creating Users

Use the CREATE USER statement as show below to create a user and assign a password:

CREATE USER 'scott' IDENTIFIED BY 'my_password';

After the user account has been created, the user can use the alter statement to modify their password.

Managing Permissions

The GRANT and REVOKE statements enable the admin user to assign user permissions based on SQL verbs such as SELECT, INSERT, UPDATE, DELETE etc. Specify an access scope with the ON clause. Use the dot notation to specify a database or a table in the database. This clause accepts a glob pattern -- use a star ('*') to specify all databases or all tables. Some examples:

Enable read access to all tables in a database:

GRANT SELECT ON 'salesforcedb'.* TO 'sf_read';

Enable read access to all databases:

GRANT SELECT ON *.* TO 'sysdba';

Default Permissions

By default, a user is created with the 'SHOW DATABASE' permissions, which allow a user to list the available databases.

Granting All Permissions

You can grant all permissions globally or at the table level:

GRANT ALL ON 'salesforcedb'.* TO 'salesforcedba';

Data Manipulation Permissions

Permissions for SQL queries can be given to a user on a specific database with the GRANT statement:

GRANT SELECT, INSERT, UPDATE, DELETE ON 'salesforcedb'.* TO 'sfuser'

Remove specific permissions using the REVOKE statement:

REVOKE INSERT, UPDATE, DELETE on 'salesforcedb'.* TO 'sfuser_readonly'

Schema Discovery Permissions

By default, newly created users are given the SHOW DATABASES permission, which allows them to view all created databases. Listing tables from a database requires SELECT permissions on this database.

Administrative Permissions

Assign the CREATE USER permission to allow users to create users:

GRANT CREATE USER, DROP USER, REVOKE ALL PRIVILEGES TO 'app_create';

Configuring OAuth

Some of the sources require authentication through OAuth. For these data sources, you will need to use the browser to login with your account and get a Verifier code. This code is then used by CData Cloud Hub to connect to that source.

Follow these steps to get the Verifier code.

  1. Navigate to the data-source specific OAuthAuthorizationURL in your browser. Refer to the documentation for the source to obtain this URL.
  2. You will be prompted for the credentials for the data source and will be asked to grant access to the CData Connector. After you login, you will be redirected to a success page, and will be given a code.
  3. Save the value of this code. This will be the value of the OAuthVerifier connection property that you will use to configure connection to this data source.

When creating the database, the DBURL attribute will have to include the OAuthVerifier. Depending on the data source, other connection properties may be required to establish the connection. See the example below which configures connection to Google Spreadsheets.

CREATE DATABASE IF NOT EXISTS MyGoogleSheets
    DRIVER = 'GoogleSheets',
    DBURL = 'OAuthVerifier=my_verifier_code'

The CData Cloud Hub will use the verifier code to get the OAuthAccessToken and RefreshToken. It will also automatically refresh the OAuthAccessToken as needed.

Security Considerations

With OAuth authentication, the tokens generated are unique to the user who logged-in and authorized the application. When creating a database connected via OAuth, you may want to consider any sensitive information before permitting other users to access this database. If confidential information is restricted per-user on a data-source, it may be safer to allow users to create their own database and manage authorization individually.

Schema Discovery

SHOW Statements

Schema information can be retrieved using SHOW queries. This query requires that the user has SELECT privileges on the target object. Here are a few examples:

List Databases:

SHOW DATABASES

List Tables in a database:

SHOW TABLES FROM my_database

List Columns from a table:

SHOW COLUMNS FROM my_database.my_table

Please refer to the MySQL documentation for more details on the SHOW query syntax.

INFORMATION_SCHEMA

Schema information can also be listed from the INFORMATION_SCHEMA views. The following views are available in this schema. Their definitions are the same as in any MySQL database.

  • TABLES
  • COLUMNS
  • VIEWS
  • KEY_COLUMN_USAGE
  • REFERENTIAL_CONSTRAINTS
  • ROUTINES

Query Syntax

You can execute SQL-92 queries against your data sources just as you would query a physical MySQL database.

Select Statements

The following syntax diagram outlines the syntax supported by the SQL engine of the driver:

SELECT {
  [ TOP <numeric_literal> | DISTINCT ]
  {
    *
    | {
        <expression> [ [ AS ] <column_reference> ]
        | { <table_name> | <correlation_name> } .*
      } [ , ... ]
  }
  [ INTO csv:// [ filename= ] <file_path> [ ;delimiter=tab ] ]
  {
    FROM <table_reference> [ [ AS ] <identifier> ]
  } [ , ... ]
  [ [ 
      INNER | { { LEFT | RIGHT | FULL } [ OUTER ] }
    ] JOIN <table_reference> [ ON <search_condition> ] [ [ AS ] <identifier> ]
  ] [ ... ]
  [ WHERE <search_condition> ]
  [ GROUP BY <column_reference> [ , ... ]
  [ HAVING <search_condition> ]
  [ UNION [ ALL ] <select_statement> ]
  [
    ORDER BY
    <column_reference> [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]
  ]
  [
    LIMIT <expression>
    [
      { OFFSET | , }
      <expression>
    ]
  ]
} | SCOPE_IDENTITY()
 
<expression> ::=
  | <column_reference>
  | @ <parameter>
  | ?
  | COUNT( * | { [ DISTINCT ] <expression> } )
  | { AVG | MAX | MIN | SUM | COUNT } ( <expression> )
  | NULLIF ( <expression> , <expression> )
  | COALESCE ( <expression> , ... )
  | CASE <expression>
      WHEN { <expression> | <search_condition> } THEN { <expression> | NULL } [ ... ]
    [ ELSE { <expression> | NULL } ]
    END
  | <literal>
  | <sql_function>
 
<search_condition> ::=
  {
    <expression> { = | > | < | >= | <= | <> | != | LIKE | NOT_LIKE | IN | NOT_IN | IS_NULL | IS_NOT_NULL | AND | OR | CONTAINS | BETWEEN } [ <expression> ]
  } [ { AND | OR } ... ]

Insert Statements

The INSERT statement specifies the columns to be inserted and the new column values. You can specify the column values in a comma-separated list in the VALUES clause:

INSERT INTO <table_name>
( <column_reference> [ , ... ] )
VALUES
( { <expression> | NULL } [ , ... ] )
   
 <expression> ::=
  | @ <parameter>
  | ?
  | <literal>

Update Statements

The UPDATE statement takes as input a comma-separated list of columns and new column values as name-value pairs in the SET clause

UPDATE <table_name> SET { <column_reference> = <expression> } [ , ... ] WHERE { Id = <expression>  } [ { AND | OR } ... ]
 
<expression> ::=
  | @ <parameter>
  | ?
  | <literal>

Delete Statements

The DELETE statement requires the table name in the FROM clause and the row's primary key in the WHERE clause.

DELETE FROM <table_name> WHERE { Id = <expression> } [ { AND | OR } ... ]

<expression> ::=
  | @ <parameter>
  | ?
  | <literal>