Authenticate via OAuth
OAuth authentication does not require any interaction with the browser to use the IBM Cloud SQL Query. The provider facilitates this process as described in Connecting to IBM Cloud
Connecting to IBM Cloud
To connect with IBM Cloud, you will need the ApiKey. You may also optionally set the Region if all your buckets belong to the same region. After setting InitiateOAuth to GETANDREFRESH (default), you are ready to connect.
When you connect, the provider completes the OAuth process.
- Extracts the access token and authenticates requests.
- Saves OAuth values in OAuthSettingsLocation to be persisted across connections.
After you have created an account in IBM Cloud you should follow the procedure below to retrieve the connection properties:
API Key
To obtain the ApiKey, take the following steps:
- Log in to your IBM Cloud account.
- Navigate to Platform API Keys page.
- On the middle right corner click "Create an IBM Cloud API Key" in order to create a new API Key.
- In the pop-up window, specify the API Key name and click "Create". Save the ApiKey somewhere as you can never access it again from the dashboard.
Cloud Object Storage CRN and SQL Query CRN
In order to connect, an CloudObjectStorageCRN and a SqlQueryCRN are required. These values will attempt to be determined automatically. If this fails or you have multiple SQL Query services, available, it is recommended to set these connection properties. To find the appropriate values, you can:
- Use the Services view. This will list your IBM Cloud services along with the CRN for each.
- Locate the CRN directly in IBM Cloud. To do so, navigate to your IBM Cloud Dashboard. In the Resource List, under Services, find your SQL Query service and select it. The CRN will be displayed. Under Storage, select your Cloud Object Storage resource to get its CRN.
- Navigate to SQL Query page, choose a name for your instance and click Create. You will be redirected to the instance of SQL Query you just created.
Register a New Instance of Cloud Object Storage
If you do not already have Cloud Object Storage in your IBM Cloud account, you can follow the procedure below to install an instance of SQL Query in your account:
- Log in to your IBM Cloud account.
- Navigate to the Cloud Object Storage page, choose a name for your instance and click Create. You will be redirected to the instance of Cloud Object Storage you just created.
Register a New Instance of SQL Query
If you do not already have SQL Query in your IBM Cloud account, you can follow the procedure below to install an instance of SQL Query in your account:
- Log in to your IBM Cloud account.
- Navigate to SQL Query page, choose a name for your instance and click Create. You will be redirected to the instance of SQL Query you just created.
Customizing IBM Cloud SQL Query Behavior
Follow the steps below to configure the provider defaults for querying IBM Cloud SQL Query.
Checking the Job Status
Once a job is initiated server-side, IBM Cloud SQL Query automatically checks for this job to finish processing. You can use PollingInterval to configure the wait time of the requests that the provider is doing towards the job status.
Note that some queries may be complex and it will take longer for the result to be retrieved, that is why the PollingInterval might be configured to be longer in order not to make so many unnecessary requests. A job is initiated server-side only when the SELECT query is not a simple "Select * FROM Table" query but it has at least a criteria or projection in it. The outcome of each job is considered a view as it supports only "Select" operation.
Cleaning Query Results
IBM Cloud SQL Query stores the results of every query you execute in CSV or JSON files in ResultLocationURI. You can use CleanQueryResults, to automatically clean these files for every query executed.
Note that this behavior will add a minor performance hit when you disconnect the last connection in a process.
Using IBM Cloud's Query Caching
You configure QueryCachingLevel to modify the usage of the query results stored in ResultLocationURI. This is especially helpful when executing a certain query multiple times. This means IBM Cloud SQL Query will not scan the same data again and simply use the results from the previous execution. These results are not used if the job was started earlier than the amount of seconds specified in QueryTolerance.
Default Bucket
A default bucket may be specified by setting the Bucket and connection property. This is optional and will be used if you do not specify a bucket when issuing a query. If no Bucket is set, then the bucket name must be used as the schema in the query in the format [Schema].[Table].
Storing Results
Results may be stored in different data types. By default, the Lyftrondata Provider for IBM Cloud SQL Query will use CSV. This may be altered by modifying StoreResultsAs.
Customizing the SSL Configuration
By default, the provider attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store. To specify another certificate, see the SSLServerCert property for the available formats to do so.
Connecting Through a Firewall or Proxy
HTTP Proxies
To connect through the Windows system proxy, you do not need to set any additional connection properties. To connect to other proxies, set ProxyAutoDetect to false.
In addition, to authenticate to an HTTP proxy, set ProxyAuthScheme, ProxyUser, and ProxyPassword, in addition to ProxyServer and ProxyPort.
Other Proxies
Set the following properties:
- To use a proxy-based firewall, set FirewallType, FirewallServer, and FirewallPort.
- To tunnel the connection, set FirewallType to TUNNEL.
- To authenticate, specify FirewallUser and FirewallPassword.
- To authenticate to a SOCKS proxy, additionally set FirewallType to SOCKS5.
Troubleshooting the Connection
To show provider activity from query execution to network traffic, use Logfile and Verbosity. The examples of common connection errors below show how to use these properties to get more context. Contact the support team for help tracing the source of an error or circumventing a performance issue.
- Authentication errors: Typically, recording a Logfile at Verbosity 4 is necessary to get full details on an authentication error.
- Queries time out: A server that takes too long to respond will exceed the provider's client-side timeout. Often, setting the Timeout property to a higher value will avoid a connection error. Another option is to disable the timeout by setting the property to 0. Setting Verbosity to 2 will show where the time is being spent.
- The certificate presented by the server cannot be validated: This error indicates that the provider cannot validate the server's certificate through the chain of trust. If you are using a self-signed certificate, there is only one certificate in the chain.
To resolve this error, you must verify yourself that the certificate can be trusted and specify to the provider that you trust the certificate. One way you can specify that you trust a certificate is to add the certificate to the trusted system store; another is to set SSLServerCert.
Generating Schema Files
The Lyftrondata Provider for IBM Cloud SQL Query enables you to persist schema definitions to configuration files. Schema files make it easy to customize and save the dynamically detected schemas, or to define your own view of the data.
You can invoke the CreateSchema stored procedure to manually generate a schema file based on the provided input parameters. This is particularly useful in situations where the name of the file in CloudStorageObject does not have (the correct) file extension.
After creating a schema, you may use the name of the generated file to query from that view.