CData Cloud offers access to Amazon S3 across several standard services and protocols, in a cloud-hosted solution. Any application that can connect to a MySQL or SQL Server database can connect to Amazon S3 through CData Cloud.
CData Cloud allows you to standardize and configure connections to Amazon S3 as though it were any other OData endpoint, or standard SQL Server/MySQL database.
The CData Cloud is designed for navigating the Amazon S3 metadata only. A variety of of stored procedures relevant to Amazon S3 data are supported as well.
This metadata typically includes details about stored objects, such as file and folder names, and excludes the actual content of the discoverable files.
If access to both the file metadata and the actual file content is needed, then the CData Cloud must be used in tandem with the associated file streaming driver(s) for the filetypes stored in Amazon S3.
The following file streaming drivers are available:
See the relevant CData file streaming driver's documentation for a configuration guide for connecting to files stored in Amazon S3.
This page provides a guide to Establishing a Connection to Amazon S3 in CData Cloud, as well as information on the available resources, and a reference to the available connection properties.
Establishing a Connection shows how to authenticate to Amazon S3 and configure any necessary connection properties to create a database in CData Cloud
Accessing data from Amazon S3 through the available standard services and CData Cloud administration is documented in further details in the CData Cloud Documentation.
Connect to Amazon S3 by selecting the corresponding icon in the Database tab. Required properties are listed under Settings. The Advanced tab lists connection properties that are not typically required.
Specify the following to connect to data:
There are several authentication methods available for connecting to Amazon S3 including: authenticating with Root Credentials, Temporary Credentials, as an AWS Role (from an EC2 Instance or by specifying the root credentials), using SSO and using a Credential File.
To obtain the credentials for an IAM user, follow the steps below:
To obtain the credentials for your AWS root account, follow the steps below:
To authenticate using account root credentials, set the following:
Note: Use of this authentication scheme is discouraged by Amazon for anything but simple tests. The account root credentials have the full permissions of the user, making this the least secure authentication method.
To authenticate using temporary credentials, specify the following:
The Cloud can now request resources using the same permissions provided by long-term credentials (such as IAM user credentials) for the lifespan of the temporary credentials.
If you are also using an IAM role to authenticate, you must additionally specify the following:
If you are using the Cloud from an EC2 Instance and have an IAM Role assigned to the instance, you can use the IAM Role to authenticate. To do so, set the following properties to authenticate:
If you are also using an IAM role to authenticate, you must additionally specify the following:
IMDSv2 Support
The Amazon S3 Cloud now supports IMDSv2. Unlike IMDSv1, the new version requires an authentication token. Endpoints and response are the same in both versions. In IMDSv2, the Amazon S3 Cloud first attempts to retrieve the IMDSv2 metadata token and then uses it to call AWS metadata endpoints. If it is unable to retrieve the token, the Cloud reverts to IMDSv1.
In many situations it may be preferable to use an IAM role for authentication instead of the direct security credentials of an AWS root user.
To authenticate as an AWS role, set the following:
Note: Roles may not be used when specifying the AWSAccessKey and AWSSecretKey of an AWS root user.
Set the AuthScheme to ADFS. The following connection properties need to be set:
AuthScheme=ADFS; AWSRegion=Ireland; [email protected]; Password=CH8WerW121235647iCa6; SSOLoginURL='https://adfs.domain.com'; AWSRoleArn=arn:aws:iam::1234:role/ADFS_SSO; AWSPrincipalArn=arn:aws:iam::1234:saml-provider/ADFSProvider;
ADFS Integrated
To use the ADFS Integrated flow, specify the SSOLoginURL and leave the username and password empty.
Set the AuthScheme to Okta. The following connection properties are used to authenticate through Okta:
then you need to use combinations of SSOProperties input parameters to authenticate using Okta. Otherwise, you do not need to set any of these values.
In SSOProperties when required, set these input parameters:
Example connection string:
AuthScheme=Okta; AWSRegion=Ireland; [email protected]; Password=CH8WerW121235647iCa6; SSOLoginURL='https://cdata-us.okta.com/home/amazon_aws/0oa35m8arsAL5f5NrE6NdA356/272'; SSOProperties='ApiToken=01230GGG2ceAnm_tPAf4MhiMELXZ0L0N1pAYrO1VR-hGQSf;'; AWSRoleArn=arn:aws:iam::1234:role/Okta_SSO; AWSPrincipalARN=arn:aws:iam::1234:saml-provider/OktaProvider;
Set the AuthScheme to PingFederate. The following connection properties need to be set:
authScheme=pingfederate;SSOLoginURL=https://mycustomserver.com:9033/idp/sts.wst;SSOExchangeUrl=https://us-east-1.signin.aws.amazon.com/platform/saml/acs/764ef411-xxxxxx;user=admin;password=PassValue;AWSPrincipalARN=arn:aws:iam::215338515180:saml-provider/pingFederate;AWSRoleArn=arn:aws:iam::215338515180:role/SSOTest2;
For users and roles that require Multi-factor Authentication, specify the following to authenticate:
Note that you can control the duration of the temporary credentials by setting the TemporaryTokenDuration property (default 3600 seconds).
You can use a credentials file to authenticate. Any configurations related to AccessKey/SecretKey authentication, temporary credentials, role authentication, or MFA can be used. To do so, set the following properties to authenticate:
If you want to use the Cloud with a user registered in a User Pool in AWS Cognito, set the following properties to authenticate:
To connect to Wasabi set AccessKey and SecretKey in the connection string.
To refine data access, set the Region to where your Wasabi data is hosted.
CustomURL should be created in this specific form:
https://s3.region.wasabisys.com/;
For more on regions, see Wasabi Regions URL
We recommend using predefined roles for services rather than creating custom IAM policies, but it if you want to create custom polices, use the roles described in the table below. Note that the specific policies required by the Amazon S3 driver are subject to change in future releases.
Amazon S3 requires at a mininum the following permissions:
| IAM Role | Description | |
| Tables: Buckets | ||
| SELECT | ListAllMyBuckets | |
| INSERT | CreateBucket | |
| DELETE | GetBucketLocation and DeleteBucket | |
| Tables: Objects | ||
| SELECT | GetBucketLocation and ListBucket | |
| DELETE | GetBucketLocation and DeleteObject | |
| Views (All views require GetBucketLocation in addition to the permission listed below) | ||
| BucketsACL | ListBucket | |
| BucketsAnalytics | GetAnalyticsConfiguration | |
| BucketsCORS | GetBucketCORS | |
| BucketsInventory | GetInventoryConfiguration | |
| BucketsLifeCycle | GetLifecycleConfiguration | |
| BucketsReplication | GetReplicationConfiguration | |
| ObjectsACL | GetObjectAcl | |
| PublicAccessBlock | GetBucketPublicAccessBlock | |
| Stored Procedures (All procedures require GetBucketLocation in addition to the permission listed below) | ||
| CopyObject | GetObject at source bucket and PutObject at destination bucket | |
| DownloadObjectTorrent | GetObjectTorrent | |
| DownloadObjects | GetObject | |
| UploadObjects | PutObject |
{
"Version": "2012-10-17",
"Statement": [
{
"Sid": "VisualEditor0",
"Effect": "Allow",
"Action": [
"s3:GetBucketPublicAccessBlock",
"s3:GetLifecycleConfiguration",
"s3:GetInventoryConfiguration",
"s3:CreateBucket",
"s3:ListBucket",
"s3:GetReplicationConfiguration",
"s3:PutObject",
"s3:GetObjectAcl",
"s3:GetObject",
"s3:GetObjectTorrent",
"s3:ListAllMyBuckets",
"s3:GetBucketCORS",
"s3:GetAnalyticsConfiguration",
"s3:DeleteObject",
"s3:GetBucketLocation",
"s3:DeleteBucket"
],
"Resource": "*"
}
]"
}
In the above example, "Resource" is set to * to allow access to all buckets, but you can limit access to only one specific bucket.
| Date | Build Number | Change Type | Description |
| 12/14/2022 | 8383 | General | Changed
|
| 09/30/2022 | 8308 | General | Changed
|
| 09/06/2022 | 8284 | Amazon S3 | Added
|
| 09/01/2022 | 8279 | Amazon S3 | Removed
|
| 08/30/2022 | 8277 | Amazon S3 | Added
|
| 08/17/2022 | 8264 | General | Changed
|
| 05/05/2022 | 8160 | Amazon S3 | Added
|
| 03/16/2022 | 8110 | Amazon S3 | Added
|
| 03/01/2022 | 8095 | Amazon S3 | Added
|
| 09/02/2021 | 7915 | General | Added
|
| 08/07/2021 | 7889 | General | Changed
|
| 08/06/2021 | 7888 | General | Changed
|
| 07/23/2021 | 7874 | General | Changed
|
| 07/23/2021 | 7874 | Amazon S3 | Added
Removed
|
| 07/08/2021 | 7859 | General | Added
|
| 05/17/2021 | 7807 | Amazon S3 | Added
|
| 04/23/2021 | 7785 | General | Added
|
| 04/23/2021 | 7783 | General | Changed
|
| 04/16/2021 | 7776 | General | Added
Changed
|
| 04/15 /2021 | 7775 | General | Changed
|
This section details a selection of advanced features of the Amazon S3 Cloud.
The Cloud allows you to define virtual tables, called user defined views, whose contents are decided by a pre-configured query. These views are useful when you cannot directly control queries being issued to the drivers. See User Defined Views for an overview of creating and configuring custom views.
Use SSL Configuration to adjust how Cloud handles TLS/SSL certificate negotiations. You can choose from various certificate formats; see the SSLServerCert property under "Connection String Options" for more information.
Configure the Cloud for compliance with Firewall and Proxy, including Windows proxies and HTTP proxies. You can also set up tunnel connections.
The Cloud offloads as much of the SELECT statement processing as possible to Amazon S3 and then processes the rest of the query in memory (client-side).
See Query Processing for more information.
See Logging for an overview of configuration settings that can be used to refine CData logging. For basic logging, you only need to set two connection properties, but there are numerous features that support more refined logging, where you can select subsets of information to be logged using the LogModules connection property.
The CData Cloud allows you to define a virtual table whose contents are decided by a pre-configured query. These are called User Defined Views, which are useful in situations where you cannot directly control the query being issued to the driver, e.g. when using the driver from a tool. The User Defined Views can be used to define predicates that are always applied. If you specify additional predicates in the query to the view, they are combined with the query already defined as part of the view.
There are two ways to create user defined views:
You can also have multiple view definitions and control them using the UserDefinedViews connection property. When you use this property, only the specified views are seen by the Cloud.
This User Defined View configuration file is formatted as follows:
For example:
{
"MyView": {
"query": "SELECT * FROM Buckets WHERE MyColumn = 'value'"
},
"MyView2": {
"query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
}
}
Use the UserDefinedViews connection property to specify the location of your JSON configuration file. For example:
"UserDefinedViews", "C:\\Users\\yourusername\\Desktop\\tmp\\UserDefinedViews.json"
SELECT * FROM Customers WHERE City = 'Raleigh';An example of a query to the driver:
SELECT * FROM UserViews.RCustomers WHERE Status = 'Active';Resulting in the effective query to the source:
SELECT * FROM Customers WHERE City = 'Raleigh' AND Status = 'Active';That is a very simple example of a query to a User Defined View that is effectively a combination of the view query and the view definition. It is possible to compose these queries in much more complex patterns. All SQL operations are allowed in both queries and are combined when appropriate.
By default, the Cloud 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.
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.
Set the following properties:
For sources that do not support SQL-92, the Cloud offloads as much of SQL statement processing as possible to Amazon S3 and then processes the rest of the query in memory (client-side). This results in optimal performance.
For data sources with limited query capabilities, the Cloud handles transformations of the SQL query to make it simpler for the Cloud. The goal is to make smart decisions based on the query capabilities of the data source to push down as much of the computation as possible. The Amazon S3 Query Evaluation component examines SQL queries and returns information indicating what parts of the query the Cloud is not capable of executing natively.
The Amazon S3 Query Slicer component is used in more specific cases to separate a single query into multiple independent queries. The client-side Query Engine makes decisions about simplifying queries, breaking queries into multiple queries, and pushing down or computing aggregations on the client-side while minimizing the size of the result set.
There's a significant trade-off in evaluating queries, even partially, client-side. There are always queries that are impossible to execute efficiently in this model, and some can be particularly expensive to compute in this manner. CData always pushes down as much of the query as is feasible for the data source to generate the most efficient query possible and provide the most flexible query capabilities.
Capturing Cloud logging can be very helpful when diagnosing error messages or other unexpected behavior.
You will simply need to set two connection properties to begin capturing Cloud logging.
Once this property is set, the Cloud will populate the log file as it carries out various tasks, such as when authentication is performed or queries are executed. If the specified file doesn't already exist, it will be created.
The verbosity level determines the amount of detail that the Cloud reports to the Logfile. Verbosity levels from 1 to 5 are supported. These are described in the following list:
| 1 | Setting Verbosity to 1 will log the query, the number of rows returned by it, the start of execution and the time taken, and any errors. |
| 2 | Setting Verbosity to 2 will log everything included in Verbosity 1 and additional information about the request. |
| 3 | Setting Verbosity to 3 will additionally log HTTP headers, as well as the body of the request and the response. |
| 4 | Setting Verbosity to 4 will additionally log transport-level communication with the data source. This includes SSL negotiation. |
| 5 | Setting Verbosity to 5 will additionally log communication with the data source and additional details that may be helpful in troubleshooting problems. This includes interface commands. |
The Verbosity should not be set to greater than 1 for normal operation. Substantial amounts of data can be logged at higher verbosities, which can delay execution times.
To refine the logged content further by showing/hiding specific categories of information, see LogModules.
Best Practices for Data Security
Although we mask sensitive values, such as passwords, in the connection string and any request in the log, it is always best practice to review the logs for any sensitive information before sharing outside your organization.
You may want to refine the exact information that is recorded to the log file. This can be accomplished using the LogModules property.
This property allows you to filter the logging using a semicolon-separated list of logging modules.
All modules are four characters long. Please note that modules containing three letters have a required trailing blank space. The available modules are:
LogModules=INFO;EXEC;SSL ;SQL ;META;
Note that these modules refine the information as it is pulled after taking the Verbosity into account.
See SELECT Statements for a syntax reference and examples.
See Data Model for information on the capabilities of the Amazon S3 API.
Use EXECUTE or EXEC statements to execute stored procedures. See EXECUTE Statements for a syntax reference and examples.
A SELECT statement can consist of the following basic clauses.
The following syntax diagram outlines the syntax supported by the SQL engine of the Cloud:
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>
]
]
}
<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 } ... ]
SELECT * FROM Buckets
SELECT [OwnerId] AS MY_OwnerId FROM Buckets
SELECT CAST(AnnualRevenue AS VARCHAR) AS Str_AnnualRevenue FROM Buckets
SELECT * FROM Buckets WHERE Name = 'TestBucket'
SELECT COUNT(*) AS MyCount FROM Buckets
SELECT COUNT(DISTINCT OwnerId) FROM Buckets
SELECT DISTINCT OwnerId FROM Buckets
SELECT OwnerId, MAX(AnnualRevenue) FROM Buckets GROUP BY OwnerIdSee Aggregate Functions for details.
SELECT Customers.ContactName, Orders.OrderDate FROM Customers, Orders WHERE Customers.CustomerId=Orders.CustomerIdSee JOIN Queries for details.
SELECT Name, OwnerId FROM Buckets ORDER BY OwnerId ASC
SELECT Name, OwnerId FROM Buckets LIMIT 10
SELECT * FROM Buckets WHERE Name = @param
Some input-only fields are available in SELECT statements. These fields, called pseudo columns, do not
appear as regular columns in the results, yet may be specified as part of the WHERE clause. You can use pseudo columns to access additional features from Amazon S3.
SELECT * FROM Buckets WHERE Query = 'Value > 100'
Returns the number of rows matching the query criteria.
SELECT COUNT(*) FROM Buckets WHERE Name = 'TestBucket'
Returns the number of distinct, non-null field values matching the query criteria.
SELECT COUNT(DISTINCT Name) AS DistinctValues FROM Buckets WHERE Name = 'TestBucket'
Returns the average of the column values.
SELECT OwnerId, AVG(AnnualRevenue) FROM Buckets WHERE Name = 'TestBucket' GROUP BY OwnerId
Returns the minimum column value.
SELECT MIN(AnnualRevenue), OwnerId FROM Buckets WHERE Name = 'TestBucket' GROUP BY OwnerId
Returns the maximum column value.
SELECT OwnerId, MAX(AnnualRevenue) FROM Buckets WHERE Name = 'TestBucket' GROUP BY OwnerId
Returns the total sum of the column values.
SELECT SUM(AnnualRevenue) FROM Buckets WHERE Name = 'TestBucket'
The CData Cloud supports standard SQL joins like the following examples.
An inner join selects only rows from both tables that match the join condition:
SELECT Customers.ContactName, Orders.OrderDate FROM Customers, Orders WHERE Customers.CustomerId=Orders.CustomerId
A left join selects all rows in the FROM table and only matching rows in the JOIN table:
SELECT Customers.ContactName, Orders.OrderDate FROM Customers LEFT OUTER JOIN Orders ON Customers.CustomerId=Orders.CustomerId
The following date literal functions can be used to filter date fields using relative intervals. Note that while the <, >, and = operators are supported for these functions, <= and >= are not.
The current day.
SELECT * FROM MyTable WHERE MyDateField = L_TODAY()
The previous day.
SELECT * FROM MyTable WHERE MyDateField = L_YESTERDAY()
The following day.
SELECT * FROM MyTable WHERE MyDateField = L_TOMORROW()
Every day in the preceding week.
SELECT * FROM MyTable WHERE MyDateField = L_LAST_WEEK()
Every day in the current week.
SELECT * FROM MyTable WHERE MyDateField = L_THIS_WEEK()
Every day in the following week.
SELECT * FROM MyTable WHERE MyDateField = L_NEXT_WEEK()Also available:
The previous n days, excluding the current day.
SELECT * FROM MyTable WHERE MyDateField = L_LAST_N_DAYS(3)
The following n days, including the current day.
SELECT * FROM MyTable WHERE MyDateField = L_NEXT_N_DAYS(3)Also available:
Every day in every week, starting n weeks before current week, and ending in the previous week.
SELECT * FROM MyTable WHERE MyDateField = L_LAST_N_WEEKS(3)
Every day in every week, starting the following week, and ending n weeks in the future.
SELECT * FROM MyTable WHERE MyDateField = L_NEXT_N_WEEKS(3)Also available:
You can use the SELECT INTO statement to export formatted data to a file.
The following query exports data into a file formatted in comma-separated values (CSV):
SELECT Name, OwnerId INTO [csv://Buckets.txt] FROM [Buckets] WHERE Name = 'TestBucket'You can specify other formats in the file URI. The possible delimiters are tab, semicolon, and comma with the default being a comma. The following example exports tab-separated values:
SELECT Name, OwnerId INTO [csv://Buckets.txt;delimiter=tab] FROM [Buckets] WHERE Name = 'TestBucket'You can specify other file formats in the URI. The following example exports tab-separated values:
The Cloud provides functions that are similar to those that are available with most standard databases. These functions are implemented in the CData provider engine and thus are available across all data sources with the same consistent API. Three categories of functions are available: string, date, and math.
The Cloud interprets all SQL function inputs as either strings or column identifiers, so you need to escape all literals as strings, with single quotes. For example, contrast the SQL Server syntax and Cloud syntax for the DATENAME function:
SELECT DATENAME(yy,GETDATE())
SELECT DATENAME('yy',GETDATE())
These functions perform string manipulations and return a string value. See STRING Functions for more details.
SELECT CONCAT(firstname, space(4), lastname) FROM Buckets WHERE Name = 'TestBucket'
These functions perform date and date time manipulations. See DATE Functions for more details.
SELECT CURRENT_TIMESTAMP() FROM Buckets
These functions provide mathematical operations. See MATH Functions for more details.
SELECT RAND() FROM Buckets
SELECT CONCAT('Mr.', SPACE(2), firstname, SPACE(4), lastname) FROM Buckets
Returns the ASCII code value of the left-most character of the character expression.
SELECT ASCII('0');
-- Result: 48
Converts the integer ASCII code to the corresponding character.
SELECT CHAR(48);
-- Result: '0'
Returns the starting position of the specified expression in the character string.
SELECT CHARINDEX('456', '0123456');
-- Result: 4
SELECT CHARINDEX('456', '0123456', 5);
-- Result: -1
Returns the number of UTF-8 characters present in the expression.
SELECT CHAR_LENGTH('sample text') FROM Account LIMIT 1
-- Result: 11
Returns the string that is the concatenation of two or more string values.
SELECT CONCAT('Hello, ', 'world!');
-- Result: 'Hello, world!'
Returns 1 if expressionToFind is found within expressionToSearch; otherwise, 0.
SELECT CONTAINS('0123456', '456');
-- Result: 1
SELECT CONTAINS('0123456', 'Not a number');
-- Result: 0
Returns 1 if character_expression ends with character_suffix; otherwise, 0.
SELECT ENDSWITH('0123456', '456');
-- Result: 1
SELECT ENDSWITH('0123456', '012');
-- Result: 0
Returns the number of bytes present in the file at the specified file path.
SELECT FILESIZE('C:/Users/User1/Desktop/myfile.txt');
-- Result: 23684
Returns the value formatted with the specified format.
SELECT FORMAT(12.34, '#');
-- Result: 12
SELECT FORMAT(12.34, '#.###');
-- Result: 12.34
SELECT FORMAT(1234, '0.000E0');
-- Result: 1.234E3
SELECT FORMAT('2019/01/01', 'yyyy-MM-dd');
-- Result: 2019-01-01
SELECT FORMAT('20190101', 'yyyyMMdd', 'yyyy-MM-dd');
-- Result: '2019-01-01'
Returns a representation of the unix_timestamp argument as a value in YYYY-MM-DD HH:MM:SS expressed in the current time zone.
SELECT FROM_UNIXTIME(1540495231, 1);
-- Result: 2018-10-25 19:20:31
SELECT FROM_UNIXTIME(1540495357385, 0);
-- Result: 2018-10-25 19:22:37
Returns the hash of the input value as a byte array using the given algorithm. The supported algorithms are MD5, SHA1, SHA2_256, SHA2_512, SHA3_224, SHA3_256, SHA3_384, and SHA3_512.
SELECT HASHBYTES('MD5', 'Test');
-- Result (byte array): 0x0CBC6611F5540BD0809A388DC95A615B
Returns the starting position of the specified expression in the character string.
SELECT INDEXOF('0123456', '456');
-- Result: 4
SELECT INDEXOF('0123456', '456', 5);
-- Result: -1
Replaces null with the specified replacement value.
SELECT ISNULL(42, 'Was NULL');
-- Result: 42
SELECT ISNULL(NULL, 'Was NULL');
-- Result: 'Was NULL'
Computes the average value of a JSON array within a JSON object. The path to the array is specified in the jsonpath argument. Return value is numeric or null.
SELECT JSON_AVG('[1,2,3,4,5]', '$[x]');
-- Result: 3
SELECT JSON_AVG('{"test": {"data": [1,2,3,4,5]}}', '$.test.data[x]');
-- Result: 3
SELECT JSON_AVG('{"test": {"data": [1,2,3,4,5]}}', '$.test.data[3..]');
-- Result: 4.5
Returns the number of elements in a JSON array within a JSON object. The path to the array is specified in the jsonpath argument. Return value is numeric or null.
SELECT JSON_COUNT('[1,2,3,4,5]', '$[x]');
-- Result: 5
SELECT JSON_COUNT('{"test": {"data": [1,2,3,4,5]}}', '$.test.data[x]');
-- Result: 5
SELECT JSON_COUNT('{"test": {"data": [1,2,3,4,5]}}', '$.test.data[3..]');
-- Result: 2
Selects any value in a JSON array or object. The path to the array is specified in the jsonpath argument. Return value is numeric or null.
SELECT JSON_EXTRACT('{"test": {"data": 1}}', '$.test');
-- Result: '{"data":1}'
SELECT JSON_EXTRACT('{"test": {"data": 1}}', '$.test.data');
-- Result: 1
SELECT JSON_EXTRACT('{"test": {"data": [1, 2, 3]}}', '$.test.data[1]');
-- Result: 2
Gets the maximum value in a JSON array within a JSON object. The path to the array is specified in the jsonpath argument. Return value is numeric or null.
SELECT JSON_MAX('[1,2,3,4,5]', '$[x]');
-- Result: 5
SELECT JSON_MAX('{"test": {"data": [1,2,3,4,5]}}', '$.test.data[x]');
-- Result: 5
SELECT JSON_MAX('{"test": {"data": [1,2,3,4,5]}}', '$.test.data[..3]');
-- Result: 4
Gets the minimum value in a JSON array within a JSON object. The path to the array is specified in the jsonpath argument. Return value is numeric or null.
SELECT JSON_MIN('[1,2,3,4,5]', '$[x]');
-- Result: 1
SELECT JSON_MIN('{"test": {"data": [1,2,3,4,5]}}', '$.test.data[x]');
-- Result: 1
SELECT JSON_MIN('{"test": {"data": [1,2,3,4,5]}}', '$.test.data[3..]');
-- Result: 4
Computes the summary value in JSON according to the JSONPath expression. Return value is numeric or null.
SELECT JSON_SUM('[1,2,3,4,5]', '$[x]');
-- Result: 15
SELECT JSON_SUM('{"test": {"data": [1,2,3,4,5]}}', '$.test.data[x]');
-- Result: 15
SELECT JSON_SUM('{"test": {"data": [1,2,3,4,5]}}', '$.test.data[3..]');
-- Result: 9
Returns the specified number of characters counting from the left of the specified string.
SELECT LEFT('1234567890', 3);
-- Result: '123'
Returns the number of characters of the specified string expression.
SELECT LEN('12345');
-- Result: 5
Returns an integer representing how many characters into the string the substring appears.
SELECT LOCATE('sample','XXXXXsampleXXXXX');
-- Result: 6
Returns the character expression with the uppercase character data converted to lowercase.
SELECT LOWER('MIXED case');
-- Result: 'mixed case'
Returns the character expression with leading blanks removed.
SELECT LTRIM(' trimmed');
-- Result: 'trimmed'
Replaces the characters between start_index and end_index with the mask_character within the string.
SELECT MASK('1234567890','*',);
-- Result: '**********'
SELECT MASK('1234567890','*', 4);
-- Result: '1234******'
SELECT MASK('1234567890','*', 4, 2);
-- Result: '1234****90'
Returns the Unicode character with the specified integer code as defined by the Unicode standard.
Returns the number of bytes present in the expression.
SELECT OCTET_LENGTH('text') FROM Account LIMIT 1
-- Result: 4
Returns the starting position of the first occurrence of the pattern in the expression. Returns 0 if the pattern is not found.
SELECT PATINDEX('123%', '1234567890');
-- Result: 1
SELECT PATINDEX('%890', '1234567890');
-- Result: 8
SELECT PATINDEX('%456%', '1234567890');
-- Result: 4
Returns the starting position of the specified expression in the character string.
SELECT POSITION('456' IN '123456');
-- Result: 4
SELECT POSITION('x' IN '123456');
-- Result: 0
Returns a valid SQL Server-delimited identifier by adding the necessary delimiters to the specified Unicode string.
SELECT QUOTENAME('table_name');
-- Result: '[table_name]'
SELECT QUOTENAME('table_name', '"');
-- Result: '"table_name"'
SELECT QUOTENAME('table_name', '[');
-- Result: '[table_name]'
Replaces all occurrences of a string with another string.
SELECT REPLACE('1234567890', '456', '|');
-- Result: '123|7890'
SELECT REPLACE('123123123', '123', '.');
-- Result: '...'
SELECT REPLACE('1234567890', 'a', 'b');
-- Result: '1234567890'
Repeats the string value the specified number of times.
SELECT REPLACE('x', 5);
-- Result: 'xxxxx'
Returns the reverse order of the string expression.
SELECT REVERSE('1234567890');
-- Result: '0987654321'
Returns the right part of the string with the specified number of characters.
SELECT RIGHT('1234567890', 3);
-- Result: '890'
Returns the character expression after it removes trailing blanks.
SELECT RTRIM('trimmed ');
-- Result: 'trimmed'
Returns the four-character Soundex code, based on how the string sounds when spoken.
SELECT SOUNDEX('smith');
-- Result: 'S530'
Returns the string that consists of repeated spaces.
SELECT SPACE(5);
-- Result: ' '
Returns a section of the string between to delimiters.
SELECT SPLIT('a/b/c/d', '/', 1);
-- Result: 'a'
SELECT SPLIT('a/b/c/d', '/', -2);
-- Result: 'c'
Returns 1 if character_expression starts with character_prefix; otherwise, 0.
SELECT STARTSWITH('0123456', '012');
-- Result: 1
SELECT STARTSWITH('0123456', '456');
-- Result: 0
Returns the character data converted from the numeric data. For example, STR(123.45, 6, 1) returns 123.5.
SELECT STR('123.456');
-- Result: '123'
SELECT STR('123.456', 2);
-- Result: '**'
SELECT STR('123.456', 10, 2);
-- Result: '123.46'
Inserts a string into another string. It deletes the specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.
SELECT STUFF('1234567890', 3, 2, 'xx');
-- Result: '12xx567890'
Returns the part of the string with the specified length; starts at the specified index.
SELECT SUBSTRING('1234567890' FROM 3 FOR 2);
-- Result: '34'
SELECT SUBSTRING('1234567890' FROM 3);
-- Result: '34567890'
Converts the value of this instance to its equivalent string representation.
SELECT TOSTRING(123);
-- Result: '123'
SELECT TOSTRING(123.456);
-- Result: '123.456'
SELECT TOSTRING(null);
-- Result: ''
Returns the character expression with leading and/or trailing blanks removed.
SELECT TRIM(' trimmed ');
-- Result: 'trimmed'
SELECT TRIM(LEADING FROM ' trimmed ');
-- Result: 'trimmed '
SELECT TRIM('-' FROM '-----trimmed-----');
-- Result: 'trimmed'
SELECT TRIM(BOTH '-' FROM '-----trimmed-----');
-- Result: 'trimmed'
SELECT TRIM(TRAILING '-' FROM '-----trimmed-----');
-- Result: '-----trimmed'
Returns the integer value defined by the Unicode standard of the first character of the input expression.
Returns the character expression with lowercase character data converted to uppercase.
SELECT UPPER('MIXED case');
-- Result: 'MIXED CASE'
Extracts an XML document using the specified XPath to flatten the XML. A comma is used to separate the outputs by default, but this can be changed by specifying the third parameter.
SELECT XML_EXTRACT('<vowels><ch>a</ch><ch>e</ch><ch>i</ch><ch>o</ch><ch>u</ch></vowels>', '/vowels/ch');
-- Result: 'a,e,i,o,u'
SELECT XML_EXTRACT('<vowels><ch>a</ch><ch>e</ch><ch>i</ch><ch>o</ch><ch>u</ch></vowels>', '/vowels/ch', ';');
-- Result: 'a;e;i;o;u'
Returns the current date value.
SELECT CURRENT_DATE();
-- Result: 2018-02-01
Returns the current time stamp of the database system as a datetime value. This value is equal to GETDATE and SYSDATETIME, and is always in the local timezone.
SELECT CURRENT_TIMESTAMP();
-- Result: 2018-02-01 03:04:05
Returns the datetime value that results from adding the specified number (a signed integer) to the specified date part of the date.
SELECT DATEADD('d', 5, '2018-02-01');
-- Result: 2018-02-06
SELECT DATEADD('hh', 5, '2018-02-01 00:00:00');
-- Result: 2018-02-01 05:00:00
Returns the difference (a signed integer) of the specified time interval between the specified start date and end date.
SELECT DATEDIFF('d', '2018-02-01', '2018-02-10');
-- Result: 9
SELECT DATEDIFF('hh', '2018-02-01 00:00:00', '2018-02-01 12:00:00');
-- Result: 12
Returns the datetime value for the specified year, month, and day.
SELECT DATEFROMPARTS(2018, 2, 1);
-- Result: 2018-02-01
Returns the character string that represents the specified date part of the specified date.
SELECT DATENAME('yy', '2018-02-01');
-- Result: '2018'
SELECT DATENAME('dw', '2018-02-01');
-- Result: 'Thursday'
Returns a character string that represents the specified date part of the specified date.
SELECT DATEPART('yy', '2018-02-01');
-- Result: 2018
SELECT DATEPART('dw', '2018-02-01');
-- Result: 5
Returns the datetime value for the specified date parts.
SELECT DATETIME2FROMPARTS(2018, 2, 1, 1, 2, 3, 456, 3);
-- Result: 2018-02-01 01:02:03.456
Returns the datetime value for the specified date parts.
SELECT DATETIMEFROMPARTS(2018, 2, 1, 1, 2, 3, 456);
-- Result: 2018-02-01 01:02:03.456
Truncates the date to the precision of the given date part. Modeled after the Oracle TRUNC function.
SELECT DATE_TRUNC('05-04-2005', 'YY');
-- Result: '1/1/2005'
SELECT DATE_TRUNC('05-04-2005', 'MM');
-- Result: '5/1/2005'
Truncates the date to the precision of the given date part. Modeled after the PostgreSQL date_trunc function.
SELECT DATE_TRUNC2('year', '2020-02-04');
-- Result: '2020-01-01'
SELECT DATE_TRUNC2('week', '2020-02-04', 'monday');
-- Result: '2020-02-02', which is the previous Monday
Returns the integer that specifies the day component of the specified date.
SELECT DAY('2018-02-01');
-- Result: 1
SELECT DAYOFMONTH('04/15/2000');
-- Result: 15
SELECT DAYOFWEEK('04/15/2000');
-- Result: 7
SELECT DAYOFYEAR('04/15/2000');
-- Result: 106
Returns the last day of the month that contains the specified date with an optional offset.
SELECT EOMONTH('2018-02-01');
-- Result: 2018-02-28
SELECT LAST_DAY('2018-02-01');
-- Result: 2018-02-28
SELECT EOMONTH('2018-02-01', 2);
-- Result: 2018-04-30
SELECT FDWEEK('02-08-2018');
-- Result: 2/4/2018
SELECT FDMONTH('02-08-2018');
-- Result: 2/1/2018
SELECT FDQUARTER('05-08-2018');
-- Result: 4/1/2018
Returns the time stamp associated with the Date Modified of the relevant file.
SELECT FILEMODIFIEDTIME('C:/Documents/myfile.txt');
-- Result: 6/25/2019 10:06:58 AM
Returns a date derived from the number of days after 1582-10-15 (based upon the Gregorian calendar). This will be equivalent to the MYSQL FROM_DAYS function.
SELECT FROM_DAYS(736000); -- Result: 2/6/2015
Returns the current time stamp of the database system as a datetime value. This value is equal to CURRENT_TIMESTAMP and SYSDATETIME, and is always in the local timezone.
SELECT GETDATE();
-- Result: 2018-02-01 03:04:05
Returns the current time stamp of the database system formatted as a UTC datetime value. This value is equal to SYSUTCDATETIME.
SELECT GETUTCDATE();
-- For example, if the local timezone is Eastern European Time (GMT+2)
-- Result: 2018-02-01 05:04:05
Returns the hour component from the provided datetime.
SELECT HOUR('02-02-2020 11:30:00');
-- Result: 11
Returns 1 if the value is a valid date, time, or datetime value; otherwise, 0.
SELECT ISDATE('2018-02-01', 'yyyy-MM-dd');
-- Result: 1
SELECT ISDATE('Not a date');
-- Result: 0
Returns a time stamp equivalent to exactly one week before the current date.
SELECT LAST_WEEK(); //Assume the date is 3/17/2020 -- Result: 3/10/2020
Returns a time stamp equivalent to exactly one month before the current date.
SELECT LAST_MONTH(); //Assume the date is 3/17/2020 -- Result: 2/17/2020
Returns a time stamp equivalent to exactly one year before the current date.
SELECT LAST_YEAR(); //Assume the date is 3/17/2020 -- Result: 3/10/2019
Returns the last day of the provided week.
SELECT LDWEEK('02-02-2020');
-- Result: 2/8/2020
Returns the last day of the provided month.
SELECT LDMONTH('02-02-2020');
-- Result: 2/29/2020
Returns the last day of the provided quarter.
SELECT LDQUARTER('02-02-2020');
-- Result: 3/31/2020
Returns a date value from a year and a number of days.
SELECT MAKEDATE(2020, 1);
-- Result: 2020-01-01
Returns the minute component from the provided datetime.
SELECT MINUTE('02-02-2020 11:15:00');
-- Result: 15
Returns the month component from the provided datetime.
SELECT MONTH('02-02-2020');
-- Result: 2
Returns the quarter associated with the provided datetime.
SELECT QUARTER('02-02-2020');
-- Result: 1
Returns the second component from the provided datetime.
SELECT SECOND('02-02-2020 11:15:23');
-- Result: 23
Returns the datetime value for the specified date and time.
SELECT SMALLDATETIMEFROMPARTS(2018, 2, 1, 1, 2);
-- Result: 2018-02-01 01:02:00
Parses the provided string value and returns the corresponding datetime.
SELECT STRTODATE('03*04*2020','dd*MM*yyyy');
-- Result: 4/3/2020
Returns the current time stamp as a datetime value of the database system. It is equal to GETDATE and CURRENT_TIMESTAMP, and is always in the local timezone.
SELECT SYSDATETIME();
-- Result: 2018-02-01 03:04:05
Returns the current system date and time as a UTC datetime value. It is equal to GETUTCDATE.
SELECT SYSUTCDATETIME();
-- For example, if the local timezone is Eastern European Time (GMT+2)
-- Result: 2018-02-01 05:04:05
Returns the time value for the specified time and with the specified precision.
SELECT TIMEFROMPARTS(1, 2, 3, 456, 3);
-- Result: 01:02:03.456
Returns the number of days since 0000-00-01. This will only return a value for dates on or after 1582-10-15 (based upon the Gregorian calendar). This will be equivalent to the MYSQL TO_DAYS function.
SELECT TO_DAYS('02-06-2015');
-- Result: 736000
Returns the week (of the year) associated with the provided datetime.
SELECT WEEK('02-17-2020 11:15:23');
-- Result: 8
Returns the integer that specifies the year of the specified date.
SELECT YEAR('2018-02-01');
-- Result: 2018
Returns the absolute (positive) value of the specified numeric expression.
SELECT ABS(15);
-- Result: 15
SELECT ABS(-15);
-- Result: 15
Returns the arc cosine, the angle in radians whose cosine is the specified float expression.
SELECT ACOS(0.5);
-- Result: 1.0471975511966
Returns the arc sine, the angle in radians whose sine is the specified float expression.
SELECT ASIN(0.5);
-- Result: 0.523598775598299
Returns the arc tangent, the angle in radians whose tangent is the specified float expression.
SELECT ATAN(10);
-- Result: 1.47112767430373
Returns the angle in radians between the positive x-axis and the ray from the origin to the point (y, x) where x and y are the values of the two specified float expressions.
SELECT ATN2(1, 1);
-- Result: 0.785398163397448
Returns the smallest integer greater than or equal to the specified numeric expression.
SELECT CEILING(1.3);
-- Result: 2
SELECT CEILING(1.5);
-- Result: 2
SELECT CEILING(1.7);
-- Result: 2
Returns the trigonometric cosine of the specified angle in radians in the specified expression.
SELECT COS(1);
-- Result: 0.54030230586814
Returns the trigonometric cotangent of the angle in radians specified by float_expression.
SELECT COT(1);
-- Result: 0.642092615934331
Returns the angle in degrees for the angle specified in radians.
SELECT DEGREES(3.1415926);
-- Result: 179.999996929531
Returns the exponential value of the specified float expression. For example, EXP(LOG(20)) is 20.
SELECT EXP(2);
-- Result: 7.38905609893065
Evaluates the expression.
SELECT EXPR('1 + 2 * 3');
-- Result: 7
SELECT EXPR('1 + 2 * 3 == 7');
-- Result: true
Returns the largest integer less than or equal to the numeric expression.
SELECT FLOOR(1.3);
-- Result: 1
SELECT FLOOR(1.5);
-- Result: 1
SELECT FLOOR(1.7);
-- Result: 1
Returns the greatest of the supplied integers.
SELECT GREATEST(3,5,8,10,1) -- Result: 10
Returns a the equivalent hex for the input value.
SELECT HEX(866849198);
-- Result: 33AB11AE
SELECT HEX('Sample Text');
-- Result: 53616D706C652054657874
Returns the least of the supplied integers.
SELECT LEAST(3,5,8,10,1) -- Result: 1
Returns the natural logarithm of the specified float expression.
SELECT LOG(7.3890560);
-- Result: 1.99999998661119
Returns the base-10 logarithm of the specified float expression.
SELECT LOG10(10000);
-- Result: 4
Returns the integer value associated with the remainder when dividing the dividend by the divisor.
SELECT MOD(10,3); -- Result: 1
Returns the opposite to the real number input.
SELECT NEGATE(10); -- Result: -10 SELECT NEGATE(-12.4) --Result: 12.4
Returns the constant value of pi.
SELECT PI()
-- Result: 3.14159265358979
Returns the value of the specified expression raised to the specified power.
SELECT POWER(2, 10);
-- Result: 1024
SELECT POWER(2, -2);
-- Result: 0.25
Returns the angle in radians of the angle in degrees.
SELECT RADIANS(180);
-- Result: 3.14159265358979
Returns a pseudorandom float value from 0 through 1, exclusive.
SELECT RAND();
-- This result may be different, since the seed is randomized
-- Result: 0.873159630165044
SELECT RAND(1);
-- This result will always be the same, since the seed is constant
-- Result: 0.248668584157093
Returns the numeric value rounded to the specified length or precision.
SELECT ROUND(1.3, 0);
-- Result: 1
SELECT ROUND(1.55, 1);
-- Result: 1.6
SELECT ROUND(1.7, 0, 0);
-- Result: 2
SELECT ROUND(1.7, 0, 1);
-- Result: 1
SELECT ROUND (1.24);
-- Result: 1.0
Returns the positive sign (1), 0, or negative sign (-1) of the specified expression.
SELECT SIGN(0);
-- Result: 0
SELECT SIGN(10);
-- Result: 1
SELECT SIGN(-10);
-- Result: -1
Returns the trigonometric sine of the angle in radians.
SELECT SIN(1);
-- Result: 0.841470984807897
Returns the square root of the specified float value.
SELECT SQRT(100);
-- Result: 10
Returns the square of the specified float value.
SELECT SQUARE(10);
-- Result: 100
SELECT SQUARE(-10);
-- Result: 100
Returns the tangent of the input expression.
SELECT TAN(1);
-- Result: 1.5574077246549
Returns the supplied decimal number truncated to have the supplied decimal precision.
SELECT TRUNC(10.3423,2); -- Result: 10.34
To execute stored procedures, you can use EXECUTE or EXEC statements.
EXEC and EXECUTE assign stored procedure inputs, referenced by name, to values or parameter names.
To execute a stored procedure as an SQL statement, use the following syntax:
{ EXECUTE | EXEC } <stored_proc_name>
{
[ @ ] <input_name> = <expression>
} [ , ... ]
<expression> ::=
| @ <parameter>
| ?
| <literal>
Reference stored procedure inputs by name:
EXECUTE my_proc @second = 2, @first = 1, @third = 3;
Execute a parameterized stored procedure statement:
EXECUTE my_proc second = @p1, first = @p2, third = @p3;
PIVOT and UNPIVOT can be used to change a table-valued expression into another table.
"SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days, [0], [1], [2], [3], [4]
FROM
(
SELECT DaysToManufacture, StandardCost
FROM Production.Product
) AS SourceTable
PIVOT
(
AVG(StandardCost)
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
) AS PivotTable;"
"SELECT VendorID, Employee, Orders
FROM
(SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
FROM pvt) p
UNPIVOT
(Orders FOR Employee IN
(Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt;"
For further information on PIVOT and UNPIVOT, see FROM clause plus JOIN, APPLY, PIVOT (Transact-SQL)
This section describes how the Cloud models the Amazon S3 API as relational Views and Stored Procedures.
Pre-defined Views are available for read only access to data from Amazon S3, including buckets, bucket information, and objects.
Stored procedures allow you to execute operations to Amazon S3, including downloading and uploading objects.
System Tables contains information about the objects and resources belonging to your database.
The Cloud models the data in Amazon S3 into a list of tables that can be queried using standard SQL statements.
Generally, querying Amazon S3 tables is the same as querying a table in a relational database. Sometimes there are special cases, for example, including a certain column in the WHERE clause might be required to get data for certain columns in the table. This is typically needed for situations where a separate request must be made for each row to get certain columns. These types of situations are clearly documented at the top of the table page linked below.
| Name | Description |
| Buckets | Returns information for buckets. Buckets are used to store objects, which consist of data and its metadata. |
| Objects | Returns information for objects inside a specific bucket. |
Returns information for buckets. Buckets are used to store objects, which consist of data and its metadata.
The Cloud uses the Amazon S3 API to process search criteria that refers to the Name column. The Cloud processes other filters client side within the Cloud. For example, the following queries are processed server side.
SELECT * FROM Buckets SELECT * FROM Buckets WHERE Name = 'TestBucket'
To insert a Bucket, specify the primary key of the Bucket in the INSERT query. For example, the following query inserts a bucket from Amazon S3:
INSERT INTO Buckets (Bucket) VALUES ('TestBucket')
To delete a Bucket, specify the primary key of the Bucket in the DELETE query. For example, the following query deletes a bucket from Amazon S3:
DELETE FROM Buckets WHERE Bucket = 'MyBucket'
| Name | Type | ReadOnly | Description |
| Bucket [KEY] | String | False |
The bucket's name. |
| CreationDate | Timestamp | False |
The date the bucket was created. |
| OwnerId | String | False |
The bucket owner's canonical user ID. |
| OwnerDisplayName | String | False |
The bucket owner's display name. |
Returns information for objects inside a specific bucket.
The Cloud uses the Amazon S3 API to process search criteria that refers to the Object and Bucket columns. The Cloud processes other filters client side within the Cloud. For example, the following queries are processed server side.
SELECT * FROM Objects SELECT * FROM Objects WHERE Object = 'TestObject' SELECT * FROM Objects WHERE Bucket = 'TestBucket' SELECT * FROM Objects WHERE Object = 'TestObject' AND Bucket = 'TestBucket'
INSERT queries are not supported to create new Objects. In order to create new Objects in Amazon S3, use the Stored Procedure UploadObject.
To delete an Object, specify the primary keys of the Object in the DELETE query. For example, the following query deletes an object from Amazon S3:
DELETE FROM Objects WHERE Object = 'MyObject' AND Bucket = 'MyBucket'
| Name | Type | ReadOnly | Description |
| Object [KEY] | String | False |
Objects's name. |
| Description | String | False |
Object's description. |
| LastModified | Datetime | False |
Date the object was last modified. |
| OwnerId | String | False |
Objects owner's canonical user ID. |
| OwnerDisplayName | String | False |
Objects owner's display name. |
| Size | String | False |
Objects size. |
| Etag | String | False |
Objects Etag. |
| Bucket [KEY] | String | False |
Bucket in which the object is located. |
Views are composed of columns and pseudo columns. Views are similar to tables in the way that data is represented; however, views do not support updates. Entities that are represented as views are typically read-only entities. Often, a stored procedure is available to update the data if such functionality is applicable to the data source.
Queries can be executed against a view as if it were a normal table, and the data that comes back is similar in that regard.
Dynamic views, such as queries exposed as views, and views for looking up specific combinations of project_team work items are supported.
| Name | Description |
| BucketsACL | Returns the access control list (ACL) of a bucket. An ACL allows you to set permissions on each object in a specific bucket. |
| BucketsAnalytics | Returns an analytics configuration (identified by the analytics configuration ID) from the bucket. |
| BucketsCompliance | Returns information for a bucket's compliance. |
| BucketsCORS | Returns the CORS configuration information set for the bucket. CORS allows cross-domain communication. |
| BucketsInventory | Returns an inventory configuration (identified by the inventory configuration ID) from the bucket. |
| BucketsLifecycle | Returns the lifecycle configuration information set on the bucket. |
| BucketsReplication | Returns a bucket's replication configuration. |
| ObjectsACL | Returns the access control list (ACL) of an object. |
| ObjectsCompliance | Returns information for objects compliance inside a specific bucket. |
| PublicAccessBlock | Retrieves the PublicAccessBlock configuration for an Amazon S3 bucket. |
Returns the access control list (ACL) of a bucket. An ACL allows you to set permissions on each object in a specific bucket.
The Cloud uses the Amazon S3 API to process search criteria that refers to the Bucket column. The Cloud processes other filters client side within the Cloud. For example, the following queries are processed server side.
SELECT * FROM BucketsACL SELECT * FROM BucketsACL WHERE Bucket = 'TestBucket'
| Name | Type | Description |
| GranteeType | String | The display name of the user. |
| GranteeId | String | The ID of the user. |
| GranteeDisplayName | String | The display name of the user. |
| GranteeURI | String | The display name of the user. |
| Permission | String | Permission given to the user or group. |
| Bucket | String | The name of the bucket that the ACL belongs to. |
Returns an analytics configuration (identified by the analytics configuration ID) from the bucket.
The Cloud uses the Amazon S3 API to process search criteria that refers to the Bucket column. The Cloud processes other filters client side within the Cloud. For example, the following queries are processed server side.
SELECT * FROM BucketsAnalytics SELECT * FROM BucketsAnalytics WHERE Bucket = 'TestBucket'
| Name | Type | Description |
| Id [KEY] | String | The ID that identifies the analytics configuration. |
| FilterPrefix | String | The prefix that an object must have to be included in the analytics results. |
| OutputSchemaVersion | String | The version of the output schema to use when exporting data. Must be V_1. |
| DestinationBucket | String | The Amazon Resource Name (ARN) of the bucket where analytics results are published. |
| DestinationFormat | String | Specifies the output format of the analytics results. |
| DestinationPrefix | String | The prefix that is prepended to all analytics results. |
| Bucket | String | The name of the current bucket. |
Returns information for a bucket's compliance.
| Name | Type | Description |
| Status | String | Either 'enabled' or 'disabled' to turn compliance on and off, respectively. |
| LockTime | String | The time at which the compliance settings are 'locked'. |
| IsLocked | Boolean | Indicator if the Bucket is locked. |
| ConditionalHold | Boolean | A Boolean value indicating if newly created objects are placed on conditional hold, meaning that they cannot be deleted until the conÂditional hold is explicitly turned off. |
| DeleteAfterRetention | Boolean | A Boolean value indicating if the object should be deleted automatically at the end of the retention period. |
| RetentionDays | Integer | An integer for the minimum number of days that objects are always retained after their creation date or release from conditional hold. |
| Bucket | String | Bucket in which the object is located. |
Returns the CORS configuration information set for the bucket. CORS allows cross-domain communication.
The Cloud uses the Amazon S3 API to process search criteria that refers to the Bucket column. The Cloud processes other filters client side within the Cloud. For example, the following queries are processed server side.
SELECT * FROM BucketsCORS SELECT * FROM BucketsCORS WHERE Bucket = 'TestBucket'
| Name | Type | Description |
| Id [KEY] | String | An optional unique identifier for the rule. |
| AllowedOrigin | String | One or more response headers that you want customers to be able to access from their applications. |
| AllowedMethod | String | Identifies an HTTP method that the domain/origin specified in the rule is allowed to execute. |
| MaxAgeSeconds | String | The time in seconds that your browser is to cache the preflight response for the specified resource. |
| AllowedHeader | String | Specifies which headers are allowed in a pre-flight OPTIONS request through the Access-Control-Request-Headers header. |
| ExposeHeader | String | One or more headers in the response that you want customers to be able to access from their applications. |
| Bucket | String | The name of the bucket that CORS belongs to. |
Returns an inventory configuration (identified by the inventory configuration ID) from the bucket.
The Cloud uses the Amazon S3 API to process search criteria that refers to the Bucket column. The Cloud processes other filters client side within the Cloud. For example, the following queries are processed server side.
SELECT * FROM BucketsInventory SELECT * FROM BucketsInventory WHERE Bucket = 'TestBucket'
| Name | Type | Description |
| Id [KEY] | String | The ID that identifies the inventory configuration. |
| IsEnabled | String | Specifies whether the inventory is enabled or disabled. |
| DestinationFormat | String | Specifies the output format of the inventory results. |
| DestinationBucket | String | The Amazon Resource Name (ARN) of the bucket where inventory results are published. |
| DestinationEncryption | String | Contains the type of server-side encryption used to encrypt the inventory results. |
| ScheduleFrequency | String | Specifies how frequently inventory results are produced. |
| IncludedObjectVersions | String | Object versions to include in the inventory list. |
| Bucket | String | The name of the current bucket. |
Returns the lifecycle configuration information set on the bucket.
The Cloud uses the Amazon S3 API to process search criteria that refers to the Bucket column. The Cloud processes other filters client side within the Cloud. For example, the following queries are processed server side.
SELECT * FROM BucketsLifecycle SELECT * FROM BucketsLifecycle WHERE Bucket = 'TestBucket'
| Name | Type | Description |
| Id [KEY] | String | Unique identifier for the rule. |
| Filter | String | Container element describing one or more filters used to identify a subset of objects to which the lifecycle rule applies. |
| Status | String | If enabled, Amazon S3 executes the rule as scheduled. If disabled, Amazon S3 ignores the rule. |
| Transition | Long | Specifies a period in the objects' lifetime when Amazon S3 should transition them to the STANDARD_IA, ONEZONE_IA, or GLACIER storage class. |
| Storage | String | Specifies the Amazon S3 storage class to which you want to transition the object. |
| Expiration | Long | Specifies a period in the object's lifetime when Amazon S3 should take the appropriate expiration action. |
| Bucket | String | The name of the current bucket. |
Returns a bucket's replication configuration.
The Cloud uses the Amazon S3 API to process search criteria that refers to the Bucket column. The Cloud processes other filters client side within the Cloud. For example, the following queries are processed server side.
SELECT * FROM BucketsReplication SELECT * FROM BucketsReplication WHERE Bucket = 'TestBucket'
| Name | Type | Description |
| Id [KEY] | String | The unique identifier for the rule. |
| Priority | String | If you specify multiple rules with overlapping filters, identifies the rule priority. |
| Status | String | Whether a rule is enabled. If Status is not set to Enabled, Amazon S3 ignores the rule |
| DeleteMarkerStatus | String | A container that describes whether Amazon S3 replicates the delete markers. |
| DestinationBucket | String | The name of the bucket where Amazon S3 stores replicas of objects identified by the rule. |
| Bucket | String | The name of the current bucket. |
Returns the access control list (ACL) of an object.
The Cloud uses the Amazon S3 API to process search criteria that refers to the Object and Bucket columns. The Cloud processes other filters client side within the Cloud. For example, the following queries are processed server side.
SELECT * FROM ObjectsACL SELECT * FROM ObjectsACL WHERE Object = 'TestObject' SELECT * FROM ObjectsACL WHERE Bucket = 'TestBucket' SELECT * FROM ObjectsACL WHERE Object = 'TestObject' AND Bucket = 'TestBucket'
| Name | Type | Description |
| GranteeType | String | Display name of the user. |
| GranteeId | String | The ID of the user. |
| GranteeDisplayName | String | Display name of the user. |
| GranteeURI | String | Display name of the user. |
| Permission | String | Permission given to the user or group. |
| Bucket | String | Name of the bucket which includes this object. |
| Object | String | Name of the object which includes this ACL. |
Returns information for objects compliance inside a specific bucket.
| Name | Type | Description |
| Object [KEY] | String | Objects's name. |
| RetentionTime | Timestamp | An ISO time giving a new retention time for the object in which the object cannot be deleted before this time. |
| ConditionalHold | Boolean | A Boolean value set to 'false' to release the object from the conditional hold setting in the bucket policy. |
| LegalHold | Boolean | A Boolean value to set the legal hold status. |
| SHA256 | String | An integer for the minimum number of days that objects are always retained after their creation date or release from conditional hold. |
| Bucket | String | Bucket in which the object is located. |
Retrieves the PublicAccessBlock configuration for an Amazon S3 bucket.
The Cloud uses the Amazon S3 API to process search criteria that refers to the Bucket column. The Cloud processes other filters client side within the Cloud. For example, the following queries are processed server side.
SELECT * FROM PublicAccessBlock SELECT * FROM PublicAccessBlock WHERE Bucket = 'TestBucket'
| Name | Type | Description |
| BlockPublicAcls | Boolean | Specifies whether Amazon S3 will block public access control lists (ACLs) for this bucket and objects in this bucket. |
| IgnorePublicAcls | Boolean | Specifies whether Amazon S3 will ignore public ACLs for this bucket and objects in this bucket. |
| BlockPublicPolicy | Boolean | Specifies whether Amazon S3 will block public bucket policies for this bucket. |
| RestrictPublicBuckets | Boolean | Specifies whether Amazon S3 will restrict public bucket policies for this bucket. |
| Bucket | String | The name of the bucket that Lifecycle belongs to. |
Stored procedures are function-like interfaces that extend the functionality of the Cloud beyond simple SELECT operations with Amazon S3.
Stored procedures accept a list of parameters, perform their intended function, and then return, if applicable, any relevant response data from Amazon S3, along with an indication of whether the procedure succeeded or failed.
| Name | Description |
| CopyObject | Copies an object from one bucket to another. |
| DeleteObject | Deletes an object from the specified bucket |
| MoveObject | Moves an object from one bucket to another. |
Copies an object from one bucket to another.
| Name | Type | Required | Description |
| BucketSource | String | True | Bucket name where the object to be copied is located. |
| ObjectSource | String | True | Object name of the object that will be copied. |
| BucketDestination | String | True | Bucket name where the object will be copied to. |
| ObjectDestination | String | False | New name of the object in the new bucket. If not specified, the name will be the same. |
| Name | Type | Description |
| Status | String | Stored procedure execution status. |
Deletes an object from the specified bucket
| Name | Type | Required | Description |
| Bucket | String | True | Bucket name where the object to be deleted is located. |
| Object | String | True | Object name of the object that will be deleted. |
| Name | Type | Description |
| Status | String | Stored procedure execution status. |
Passes the name of a bucket and object to download.
| Name | Type | Required | Accepts Output Streams | Description |
| Bucket | String | True | False | Bucket name where the object is located. |
| Object | String | False | False | Object name for the object that should be retrieved. If not specified, all the objects from the specified bucket will be retrieved. |
| LocalFolderPath | String | False | False | The path, or URI, to the file that will receive the data of the object. |
| ByteRangeStart | Long | False | False | The start of the byte range to download. |
| ByteRangeEnd | Long | False | False | The end of the byte range to download. |
| UpdatedStartDate | Datetime | False | False | The start of the date range to download objects. If not specified, objects will be downloaded from the beginning of the time until the UpdatedEndDate. |
| UpdatedEndDate | Datetime | False | False | The end of the date range to download objects. If not specified, objects will be downloaded from the specified UpdatedStartDate until the present day. |
| FileStream | String | False | True | An instance of an output stream where file data is written to. Only used if LocalFolderPath is not set. |
| Name | Type | Description |
| Status | String | Stored procedure execution status. |
| Content | String | If the LocalFolderPath input is empty the file content will be outputted as base64. |
Moves an object from one bucket to another.
| Name | Type | Required | Description |
| BucketSource | String | True | Bucket name where the object to be moved is located. |
| ObjectSource | String | True | Object name of the object that will be moved. |
| BucketDestination | String | True | Bucket name where the object will be moved to. |
| ObjectDestination | String | False | New name of the object in the new bucket. If not specified, the name will be the same. |
| Name | Type | Description |
| Status | String | Stored procedure execution status. |
Uploads objects in a single operation.
| Name | Type | Required | Accepts Input Streams | Description |
| Bucket | String | True | False | Bucket name of the object location. |
| FolderPath | String | False | False | The path to the folder that receives the data of the object. |
| ChunkSize | Integer | False | False | The chunk size in MB for multi-part uploads. The value must be 5 MB or larger.
The default value is 15. |
| LocalFilePath | String | False | False | The path to the file that is uploaded in the bucket. If this is a path to a folder, then all the files in the folder are uploaded in the bucket. |
| Access | String | False | False | The access policy for this object.
The allowed values are PRIVATE, ANONREAD, ANONREADWRITE, AUTHREAD. The default value is PRIVATE. |
| ObjectInformationAggregate | String | False | False | An aggregate representing the object information. Can be in the form of XML, JSON or a #TEMP table. |
| Content | String | False | True | The content as InputStream to be uploaded when LocalFilePath or FolderPath is not specified. |
| FileName | String | False | False | FileName uploaded in Amazon S3. Required when FileContent is specified. |
| Name | Type | Description |
| Status | String | Stored procedure execution status. |
You can query the system tables described in this section to access schema information, information on data source functionality, and batch operation statistics.
The following tables return database metadata for Amazon S3:
The following tables return information about how to connect to and query the data source:
The following table returns query statistics for data modification queries:
Lists the available databases.
The following query retrieves all databases determined by the connection string:
SELECT * FROM sys_catalogs
| Name | Type | Description |
| CatalogName | String | The database name. |
Lists the available schemas.
The following query retrieves all available schemas:
SELECT * FROM sys_schemas
| Name | Type | Description |
| CatalogName | String | The database name. |
| SchemaName | String | The schema name. |
Lists the available tables.
The following query retrieves the available tables and views:
SELECT * FROM sys_tables
| Name | Type | Description |
| CatalogName | String | The database containing the table or view. |
| SchemaName | String | The schema containing the table or view. |
| TableName | String | The name of the table or view. |
| TableType | String | The table type (table or view). |
| Description | String | A description of the table or view. |
| IsUpdateable | Boolean | Whether the table can be updated. |
Describes the columns of the available tables and views.
The following query returns the columns and data types for the Buckets table:
SELECT ColumnName, DataTypeName FROM sys_tablecolumns WHERE TableName='Buckets'
| Name | Type | Description |
| CatalogName | String | The name of the database containing the table or view. |
| SchemaName | String | The schema containing the table or view. |
| TableName | String | The name of the table or view containing the column. |
| ColumnName | String | The column name. |
| DataTypeName | String | The data type name. |
| DataType | Int32 | An integer indicating the data type. This value is determined at run time based on the environment. |
| Length | Int32 | The storage size of the column. |
| DisplaySize | Int32 | The designated column's normal maximum width in characters. |
| NumericPrecision | Int32 | The maximum number of digits in numeric data. The column length in characters for character and date-time data. |
| NumericScale | Int32 | The column scale or number of digits to the right of the decimal point. |
| IsNullable | Boolean | Whether the column can contain null. |
| Description | String | A brief description of the column. |
| Ordinal | Int32 | The sequence number of the column. |
| IsAutoIncrement | String | Whether the column value is assigned in fixed increments. |
| IsGeneratedColumn | String | Whether the column is generated. |
| IsHidden | Boolean | Whether the column is hidden. |
| IsArray | Boolean | Whether the column is an array. |
Lists the available stored procedures.
The following query retrieves the available stored procedures:
SELECT * FROM sys_procedures
| Name | Type | Description |
| CatalogName | String | The database containing the stored procedure. |
| SchemaName | String | The schema containing the stored procedure. |
| ProcedureName | String | The name of the stored procedure. |
| Description | String | A description of the stored procedure. |
| ProcedureType | String | The type of the procedure, such as PROCEDURE or FUNCTION. |
Describes stored procedure parameters.
The following query returns information about all of the input parameters for the DownloadObjects stored procedure:
SELECT * FROM sys_procedureparameters WHERE ProcedureName='DownloadObjects' AND Direction=1 OR Direction=2
| Name | Type | Description |
| CatalogName | String | The name of the database containing the stored procedure. |
| SchemaName | String | The name of the schema containing the stored procedure. |
| ProcedureName | String | The name of the stored procedure containing the parameter. |
| ColumnName | String | The name of the stored procedure parameter. |
| Direction | Int32 | An integer corresponding to the type of the parameter: input (1), input/output (2), or output(4). input/output type parameters can be both input and output parameters. |
| DataTypeName | String | The name of the data type. |
| DataType | Int32 | An integer indicating the data type. This value is determined at run time based on the environment. |
| Length | Int32 | The number of characters allowed for character data. The number of digits allowed for numeric data. |
| NumericPrecision | Int32 | The maximum precision for numeric data. The column length in characters for character and date-time data. |
| NumericScale | Int32 | The number of digits to the right of the decimal point in numeric data. |
| IsNullable | Boolean | Whether the parameter can contain null. |
| IsRequired | Boolean | Whether the parameter is required for execution of the procedure. |
| IsArray | Boolean | Whether the parameter is an array. |
| Description | String | The description of the parameter. |
| Ordinal | Int32 | The index of the parameter. |
Describes the primary and foreign keys.
The following query retrieves the primary key for the Buckets table:
SELECT * FROM sys_keycolumns WHERE IsKey='True' AND TableName='Buckets'
| Name | Type | Description |
| CatalogName | String | The name of the database containing the key. |
| SchemaName | String | The name of the schema containing the key. |
| TableName | String | The name of the table containing the key. |
| ColumnName | String | The name of the key column. |
| IsKey | Boolean | Whether the column is a primary key in the table referenced in the TableName field. |
| IsForeignKey | Boolean | Whether the column is a foreign key referenced in the TableName field. |
| PrimaryKeyName | String | The name of the primary key. |
| ForeignKeyName | String | The name of the foreign key. |
| ReferencedCatalogName | String | The database containing the primary key. |
| ReferencedSchemaName | String | The schema containing the primary key. |
| ReferencedTableName | String | The table containing the primary key. |
| ReferencedColumnName | String | The column name of the primary key. |
Describes the foreign keys.
The following query retrieves all foreign keys which refer to other tables:
SELECT * FROM sys_foreignkeys WHERE ForeignKeyType = 'FOREIGNKEY_TYPE_IMPORT'
| Name | Type | Description |
| CatalogName | String | The name of the database containing the key. |
| SchemaName | String | The name of the schema containing the key. |
| TableName | String | The name of the table containing the key. |
| ColumnName | String | The name of the key column. |
| PrimaryKeyName | String | The name of the primary key. |
| ForeignKeyName | String | The name of the foreign key. |
| ReferencedCatalogName | String | The database containing the primary key. |
| ReferencedSchemaName | String | The schema containing the primary key. |
| ReferencedTableName | String | The table containing the primary key. |
| ReferencedColumnName | String | The column name of the primary key. |
| ForeignKeyType | String | Designates whether the foreign key is an import (points to other tables) or export (referenced from other tables) key. |
Describes the available indexes. By filtering on indexes, you can write more selective queries with faster query response times.
The following query retrieves all indexes that are not primary keys:
SELECT * FROM sys_indexes WHERE IsPrimary='false'
| Name | Type | Description |
| CatalogName | String | The name of the database containing the index. |
| SchemaName | String | The name of the schema containing the index. |
| TableName | String | The name of the table containing the index. |
| IndexName | String | The index name. |
| ColumnName | String | The name of the column associated with the index. |
| IsUnique | Boolean | True if the index is unique. False otherwise. |
| IsPrimary | Boolean | True if the index is a primary key. False otherwise. |
| Type | Int16 | An integer value corresponding to the index type: statistic (0), clustered (1), hashed (2), or other (3). |
| SortOrder | String | The sort order: A for ascending or D for descending. |
| OrdinalPosition | Int16 | The sequence number of the column in the index. |
Returns information on the available connection properties and those set in the connection string.
When querying this table, the config connection string should be used:
jdbc:cdata:amazons3:config:
This connection string enables you to query this table without a valid connection.
The following query retrieves all connection properties that have been set in the connection string or set through a default value:
SELECT * FROM sys_connection_props WHERE Value <> ''
| Name | Type | Description |
| Name | String | The name of the connection property. |
| ShortDescription | String | A brief description. |
| Type | String | The data type of the connection property. |
| Default | String | The default value if one is not explicitly set. |
| Values | String | A comma-separated list of possible values. A validation error is thrown if another value is specified. |
| Value | String | The value you set or a preconfigured default. |
| Required | Boolean | Whether the property is required to connect. |
| Category | String | The category of the connection property. |
| IsSessionProperty | String | Whether the property is a session property, used to save information about the current connection. |
| Sensitivity | String | The sensitivity level of the property. This informs whether the property is obfuscated in logging and authentication forms. |
| PropertyName | String | A camel-cased truncated form of the connection property name. |
| Ordinal | Int32 | The index of the parameter. |
| CatOrdinal | Int32 | The index of the parameter category. |
| Hierarchy | String | Shows dependent properties associated that need to be set alongside this one. |
| Visible | Boolean | Informs whether the property is visible in the connection UI. |
| ETC | String | Various miscellaneous information about the property. |
Describes the SELECT query processing that the Cloud can offload to the data source.
See SQL Compliance for SQL syntax details.
Below is an example data set of SQL capabilities. The following result set indicates the SELECT functionality that the Cloud can offload to the data source or process client side. Your data source may support additional SQL syntax. Some aspects of SELECT functionality are returned in a comma-separated list if supported; otherwise, the column contains NO.
| Name | Description | Possible Values |
| AGGREGATE_FUNCTIONS | Supported aggregation functions. | AVG, COUNT, MAX, MIN, SUM, DISTINCT |
| COUNT | Whether COUNT function is supported. | YES, NO |
| IDENTIFIER_QUOTE_OPEN_CHAR | The opening character used to escape an identifier. | [ |
| IDENTIFIER_QUOTE_CLOSE_CHAR | The closing character used to escape an identifier. | ] |
| SUPPORTED_OPERATORS | A list of supported SQL operators. | =, >, <, >=, <=, <>, !=, LIKE, NOT LIKE, IN, NOT IN, IS NULL, IS NOT NULL, AND, OR |
| GROUP_BY | Whether GROUP BY is supported, and, if so, the degree of support. | NO, NO_RELATION, EQUALS_SELECT, SQL_GB_COLLATE |
| OJ_CAPABILITIES | The supported varieties of outer joins supported. | NO, LEFT, RIGHT, FULL, INNER, NOT_ORDERED, ALL_COMPARISON_OPS |
| OUTER_JOINS | Whether outer joins are supported. | YES, NO |
| SUBQUERIES | Whether subqueries are supported, and, if so, the degree of support. | NO, COMPARISON, EXISTS, IN, CORRELATED_SUBQUERIES, QUANTIFIED |
| STRING_FUNCTIONS | Supported string functions. | LENGTH, CHAR, LOCATE, REPLACE, SUBSTRING, RTRIM, LTRIM, RIGHT, LEFT, UCASE, SPACE, SOUNDEX, LCASE, CONCAT, ASCII, REPEAT, OCTET, BIT, POSITION, INSERT, TRIM, UPPER, REGEXP, LOWER, DIFFERENCE, CHARACTER, SUBSTR, STR, REVERSE, PLAN, UUIDTOSTR, TRANSLATE, TRAILING, TO, STUFF, STRTOUUID, STRING, SPLIT, SORTKEY, SIMILAR, REPLICATE, PATINDEX, LPAD, LEN, LEADING, KEY, INSTR, INSERTSTR, HTML, GRAPHICAL, CONVERT, COLLATION, CHARINDEX, BYTE |
| NUMERIC_FUNCTIONS | Supported numeric functions. | ABS, ACOS, ASIN, ATAN, ATAN2, CEILING, COS, COT, EXP, FLOOR, LOG, MOD, SIGN, SIN, SQRT, TAN, PI, RAND, DEGREES, LOG10, POWER, RADIANS, ROUND, TRUNCATE |
| TIMEDATE_FUNCTIONS | Supported date/time functions. | NOW, CURDATE, DAYOFMONTH, DAYOFWEEK, DAYOFYEAR, MONTH, QUARTER, WEEK, YEAR, CURTIME, HOUR, MINUTE, SECOND, TIMESTAMPADD, TIMESTAMPDIFF, DAYNAME, MONTHNAME, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, EXTRACT |
| REPLICATION_SKIP_TABLES | Indicates tables skipped during replication. | |
| REPLICATION_TIMECHECK_COLUMNS | A string array containing a list of columns which will be used to check for (in the given order) to use as a modified column during replication. | |
| IDENTIFIER_PATTERN | String value indicating what string is valid for an identifier. | |
| SUPPORT_TRANSACTION | Indicates if the provider supports transactions such as commit and rollback. | YES, NO |
| DIALECT | Indicates the SQL dialect to use. | |
| KEY_PROPERTIES | Indicates the properties which identify the uniform database. | |
| SUPPORTS_MULTIPLE_SCHEMAS | Indicates if multiple schemas may exist for the provider. | YES, NO |
| SUPPORTS_MULTIPLE_CATALOGS | Indicates if multiple catalogs may exist for the provider. | YES, NO |
| DATASYNCVERSION | The CData Data Sync version needed to access this driver. | Standard, Starter, Professional, Enterprise |
| DATASYNCCATEGORY | The CData Data Sync category of this driver. | Source, Destination, Cloud Destination |
| SUPPORTSENHANCEDSQL | Whether enhanced SQL functionality beyond what is offered by the API is supported. | TRUE, FALSE |
| SUPPORTS_BATCH_OPERATIONS | Whether batch operations are supported. | YES, NO |
| SQL_CAP | All supported SQL capabilities for this driver. | SELECT, INSERT, DELETE, UPDATE, TRANSACTIONS, ORDERBY, OAUTH, ASSIGNEDID, LIMIT, LIKE, BULKINSERT, COUNT, BULKDELETE, BULKUPDATE, GROUPBY, HAVING, AGGS, OFFSET, REPLICATE, COUNTDISTINCT, JOINS, DROP, CREATE, DISTINCT, INNERJOINS, SUBQUERIES, ALTER, MULTIPLESCHEMAS, GROUPBYNORELATION, OUTERJOINS, UNIONALL, UNION, UPSERT, GETDELETED, CROSSJOINS, GROUPBYCOLLATE, MULTIPLECATS, FULLOUTERJOIN, MERGE, JSONEXTRACT, BULKUPSERT, SUM, SUBQUERIESFULL, MIN, MAX, JOINSFULL, XMLEXTRACT, AVG, MULTISTATEMENTS, FOREIGNKEYS, CASE, LEFTJOINS, COMMAJOINS, WITH, LITERALS, RENAME, NESTEDTABLES, EXECUTE, BATCH, BASIC, INDEX |
| PREFERRED_CACHE_OPTIONS | A string value specifies the preferred cacheOptions. | |
| ENABLE_EF_ADVANCED_QUERY | Indicates if the driver directly supports advanced queries coming from Entity Framework. If not, queries will be handled client side. | YES, NO |
| PSEUDO_COLUMNS | A string array indicating the available pseudo columns. | |
| MERGE_ALWAYS | If the value is true, The Merge Mode is forcibly executed in Data Sync. | TRUE, FALSE |
| REPLICATION_MIN_DATE_QUERY | A select query to return the replicate start datetime. | |
| REPLICATION_MIN_FUNCTION | Allows a provider to specify the formula name to use for executing a server side min. | |
| REPLICATION_START_DATE | Allows a provider to specify a replicate startdate. | |
| REPLICATION_MAX_DATE_QUERY | A select query to return the replicate end datetime. | |
| REPLICATION_MAX_FUNCTION | Allows a provider to specify the formula name to use for executing a server side max. | |
| IGNORE_INTERVALS_ON_INITIAL_REPLICATE | A list of tables which will skip dividing the replicate into chunks on the initial replicate. | |
| CHECKCACHE_USE_PARENTID | Indicates whether the CheckCache statement should be done against the parent key column. | TRUE, FALSE |
| CREATE_SCHEMA_PROCEDURES | Indicates stored procedures that can be used for generating schema files. |
The following query retrieves the operators that can be used in the WHERE clause:
SELECT * FROM sys_sqlinfo WHERE Name='SUPPORTED_OPERATORS'
Note that individual tables may have different limitations or requirements on the WHERE clause; refer to the Data Model section for more information.
| Name | Type | Description |
| NAME | String | A component of SQL syntax, or a capability that can be processed on the server. |
| VALUE | String | Detail on the supported SQL or SQL syntax. |
Returns information about attempted modifications.
The following query retrieves the Ids of the modified rows in a batch operation:
SELECT * FROM sys_identity
| Name | Type | Description |
| Id | String | The database-generated Id returned from a data modification operation. |
| Batch | String | An identifier for the batch. 1 for a single operation. |
| Operation | String | The result of the operation in the batch: INSERTED, UPDATED, or DELETED. |
| Message | String | SUCCESS or an error message if the update in the batch failed. |
The connection string properties are the various options that can be used to establish a connection. This section provides a complete list of the options you can configure in the connection string for this provider. Click the links for further details.
| Property | Description |
| CustomURL | The custom URL to the S3 based service. Specify this URL if the S3 based service has a different URL from the 'amazonaws.com'. Make sure to specify the full URL. For example: CustomURL=http://127.0.0.1:9000. Please note that in a custom S3 based service, views other than Buckets and Objects might not be supported to work with or might need to be configured on the custom service itself. |
| SimpleUploadLimit | This setting specifies the threshold, in bytes, above which the provider will choose to perform a multipart upload rather than uploading everything in one request. |
| UseVirtualHosting | If true (default), buckets will be referenced in the request using the hosted-style request: http://bucket-name.host/yourobject. If set to false, the bean will use the path-style request: http://host/bucket-name/yourobject. |
| Property | Description |
| AuthScheme | The scheme used for authentication. Accepted entries are: Auto, , AwsRootKeys , AwsIAMRoles , AwsEC2Roles , AwsMFA , ADFS, Okta, PingFederate , AwsCredentialsFile , AwsCognitoBasic , AwsCognitoSrp. |
| AWSAccessKey | Your AWS account access key. This value is accessible from your AWS security credentials page. |
| AWSSecretKey | Your AWS account secret key. This value is accessible from your AWS security credentials page. |
| AWSRoleARN | The Amazon Resource Name of the role to use when authenticating. |
| AWSPrincipalARN | The ARN of the SAML Identity provider in your AWS account. |
| AWSRegion | The hosting region for your Amazon Web Services. |
| AWSSessionToken | Your AWS session token. |
| AWSExternalId | A unique identifier that might be required when you assume a role in another account. |
| MFASerialNumber | The serial number of the MFA device if one is being used. |
| MFAToken | The temporary token available from your MFA device. |
| TemporaryTokenDuration | The amount of time (in seconds) a temporary token will last. |
| AWSCognitoRegion | The hosting region for AWS Cognito. |
| AWSUserPoolId | The User Pool Id. |
| AWSUserPoolClientAppId | The User Pool Client App Id. |
| AWSUserPoolClientAppSecret | Optional. The User Pool Client App Secret. |
| AWSIdentityPoolId | The Identity Pool Id. |
| ServerSideEncryption | When activated, file uploads into Amazon S3 buckets will be server-side encrypted. |
| Property | Description |
| User | The IDP user used to authenticate the IDP via SSO. |
| Password | The password used to authenticate the IDP user via SSO. |
| SSOLoginURL | The identity provider's login URL. |
| SSOProperties | Additional properties required to connect to the identity provider in a semicolon-separated list. |
| SSOExchangeUrl | The url used for consuming the SAML response and exchanging it with Amazon S3 specific credentials. |
| Property | Description |
| SSLServerCert | The certificate to be accepted from the server when connecting using TLS/SSL. |
| Property | Description |
| FirewallType | The protocol used by a proxy-based firewall. |
| FirewallServer | The name or IP address of a proxy-based firewall. |
| FirewallPort | The TCP port for a proxy-based firewall. |
| FirewallUser | The user name to use to authenticate with a proxy-based firewall. |
| FirewallPassword | A password used to authenticate to a proxy-based firewall. |
| Property | Description |
| ProxyAutoDetect | This indicates whether to use the system proxy settings or not. This takes precedence over other proxy settings, so you'll need to set ProxyAutoDetect to FALSE in order use custom proxy settings. |
| ProxyServer | The hostname or IP address of a proxy to route HTTP traffic through. |
| ProxyPort | The TCP port the ProxyServer proxy is running on. |
| ProxyAuthScheme | The authentication type to use to authenticate to the ProxyServer proxy. |
| ProxyUser | A user name to be used to authenticate to the ProxyServer proxy. |
| ProxyPassword | A password to be used to authenticate to the ProxyServer proxy. |
| ProxySSLType | The SSL type to use when connecting to the ProxyServer proxy. |
| ProxyExceptions | A semicolon separated list of destination hostnames or IPs that are exempt from connecting through the ProxyServer . |
| Property | Description |
| Logfile | A filepath which designates the name and location of the log file. |
| Verbosity | The verbosity level that determines the amount of detail included in the log file. |
| LogModules | Core modules to be included in the log file. |
| MaxLogFileSize | A string specifying the maximum size in bytes for a log file (for example, 10 MB). |
| MaxLogFileCount | A string specifying the maximum file count of log files. |
| Property | Description |
| Location | A path to the directory that contains the schema files defining tables, views, and stored procedures. |
| BrowsableSchemas | This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC. |
| Tables | This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC. |
| Views | Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC. |
| Property | Description |
| AutoCache | Automatically caches the results of SELECT queries into a cache database specified by either CacheLocation or both of CacheConnection and CacheProvider . |
| CacheLocation | Specifies the path to the cache when caching to a file. |
| CacheTolerance | The tolerance for stale data in the cache specified in seconds when using AutoCache . |
| Offline | Use offline mode to get the data from the cache instead of the live source. |
| CacheMetadata | This property determines whether or not to cache the table metadata to a file store. |
| Property | Description |
| CreateFoldersOnDownload | Automatically create the destination folder during execution of DownloadObjects Stored Procedure. |
| EncodeFilename | Determines if the local file path in DownloadObjects Stored Procedure should be encoded. |
| MaxRows | Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time. |
| MaxThreads | Specifies the number of concurrent requests. |
| Other | These hidden properties are used only in specific use cases. |
| PseudoColumns | This property indicates whether or not to include pseudo columns as columns to the table. |
| RTK | The runtime key used for licensing. |
| Timeout | The value in seconds until the timeout error is thrown, canceling the operation. |
| UserDefinedViews | A filepath pointing to the JSON configuration file containing your custom views. |
This section provides a complete list of the Connection properties you can configure in the connection string for this provider.
| Property | Description |
| CustomURL | The custom URL to the S3 based service. Specify this URL if the S3 based service has a different URL from the 'amazonaws.com'. Make sure to specify the full URL. For example: CustomURL=http://127.0.0.1:9000. Please note that in a custom S3 based service, views other than Buckets and Objects might not be supported to work with or might need to be configured on the custom service itself. |
| SimpleUploadLimit | This setting specifies the threshold, in bytes, above which the provider will choose to perform a multipart upload rather than uploading everything in one request. |
| UseVirtualHosting | If true (default), buckets will be referenced in the request using the hosted-style request: http://bucket-name.host/yourobject. If set to false, the bean will use the path-style request: http://host/bucket-name/yourobject. |
The custom URL to the S3 based service. Specify this URL if the S3 based service has a different URL from the 'amazonaws.com'. Make sure to specify the full URL. For example: CustomURL=http://127.0.0.1:9000. Please note that in a custom S3 based service, views other than Buckets and Objects might not be supported to work with or might need to be configured on the custom service itself.
string
""
The custom URL to the S3 based service. Specify this URL if the S3 based service has a different URL from the 'amazonaws.com'. Make sure to specify the full URL. For example: CustomURL=http://127.0.0.1:9000. Please note that in a custom S3 based service, views other than Buckets and Objects might not be supported to work with or might need to be configured on the custom service itself.
This setting specifies the threshold, in bytes, above which the provider will choose to perform a multipart upload rather than uploading everything in one request.
string
""
This setting specifies the threshold, in bytes, above which the Cloud will choose to perform a multipart upload rather than uploading everything in one request.
If true (default), buckets will be referenced in the request using the hosted-style request: http://bucket-name.host/yourobject. If set to false, the bean will use the path-style request: http://host/bucket-name/yourobject.
bool
true
If true (default), buckets will be referenced in the request using the hosted-style request: http://bucket-name.host/yourobject. If set to false, the bean will use the path-style request: http://host/bucket-name/yourobject.
This section provides a complete list of the AWS Authentication properties you can configure in the connection string for this provider.
| Property | Description |
| AuthScheme | The scheme used for authentication. Accepted entries are: Auto, , AwsRootKeys , AwsIAMRoles , AwsEC2Roles , AwsMFA , ADFS, Okta, PingFederate , AwsCredentialsFile , AwsCognitoBasic , AwsCognitoSrp. |
| AWSAccessKey | Your AWS account access key. This value is accessible from your AWS security credentials page. |
| AWSSecretKey | Your AWS account secret key. This value is accessible from your AWS security credentials page. |
| AWSRoleARN | The Amazon Resource Name of the role to use when authenticating. |
| AWSPrincipalARN | The ARN of the SAML Identity provider in your AWS account. |
| AWSRegion | The hosting region for your Amazon Web Services. |
| AWSSessionToken | Your AWS session token. |
| AWSExternalId | A unique identifier that might be required when you assume a role in another account. |
| MFASerialNumber | The serial number of the MFA device if one is being used. |
| MFAToken | The temporary token available from your MFA device. |
| TemporaryTokenDuration | The amount of time (in seconds) a temporary token will last. |
| AWSCognitoRegion | The hosting region for AWS Cognito. |
| AWSUserPoolId | The User Pool Id. |
| AWSUserPoolClientAppId | The User Pool Client App Id. |
| AWSUserPoolClientAppSecret | Optional. The User Pool Client App Secret. |
| AWSIdentityPoolId | The Identity Pool Id. |
| ServerSideEncryption | When activated, file uploads into Amazon S3 buckets will be server-side encrypted. |
The scheme used for authentication. Accepted entries are: Auto, , AwsRootKeys , AwsIAMRoles , AwsEC2Roles , AwsMFA , ADFS, Okta, PingFederate , AwsCredentialsFile , AwsCognitoBasic , AwsCognitoSrp.
string
"Auto"
Use the following options to select your authentication scheme:
Your AWS account access key. This value is accessible from your AWS security credentials page.
string
""
Your AWS account access key. This value is accessible from your AWS security credentials page:
Your AWS account secret key. This value is accessible from your AWS security credentials page.
string
""
Your AWS account secret key. This value is accessible from your AWS security credentials page:
The Amazon Resource Name of the role to use when authenticating.
string
""
When authenticating outside of AWS, it is common to use a Role for authentication instead of your direct AWS account credentials. Entering the AWSRoleARN will cause the CData Cloud to perform a role based authentication instead of using the AWSAccessKey and AWSSecretKey directly. The AWSAccessKey and AWSSecretKey must still be specified to perform this authentication. You cannot use the credentials of an AWS root user when setting RoleARN. The AWSAccessKey and AWSSecretKey must be those of an IAM user.
The ARN of the SAML Identity provider in your AWS account.
string
""
The ARN of the SAML Identity provider in your AWS account.
The hosting region for your Amazon Web Services.
string
"NORTHERNVIRGINIA"
The hosting region for your Amazon Web Services. Available values are OHIO, NORTHERNVIRGINIA, NORTHERNCALIFORNIA, OREGON, CAPETOWN, HONGKONG, JAKARTA, MUMBAI, OSAKA, SEOUL, SINGAPORE, SYDNEY, TOKYO, CENTRAL, BEIJING, NINGXIA, FRANKFURT, IRELAND, LONDON, MILAN, PARIS, STOCKHOLM, ZURICH, BAHRAIN, UAE, SAOPAULO, GOVCLOUDEAST, and GOVCLOUDWEST.
Your AWS session token.
string
""
Your AWS session token. This value can be retrieved in different ways. See this link for more info.
A unique identifier that might be required when you assume a role in another account.
string
""
A unique identifier that might be required when you assume a role in another account.
The serial number of the MFA device if one is being used.
string
""
You can find the device for an IAM user by going to the AWS Management Console and viewing the user's security credentials. For virtual devices, this is actually an Amazon Resource Name (such as arn:aws:iam::123456789012:mfa/user).
The temporary token available from your MFA device.
string
""
If MFA is required, this value will be used along with the MFASerialNumber to retrieve temporary credentials to login. The temporary credentials available from AWS will only last up to 1 hour by default (see TemporaryTokenDuration). Once the time is up, the connection must be updated to specify a new MFA token so that new credentials may be obtained.
The amount of time (in seconds) a temporary token will last.
string
"3600"
Temporary tokens are used with both MFA and Role based authentication. Temporary tokens will eventually time out, at which time a new temporary token must be obtained. For situations where MFA is not used, this is not a big deal. The CData Cloud will internally request a new temporary token once the temporary token has expired.
However, for MFA required connection, a new MFAToken must be specified in the connection to retrieve a new temporary token. This is a more intrusive issue since it requires an update to the connection by the user. The maximum and minimum that can be specified will depend largely on the connection being used.
For Role based authentication, the minimum duration is 900 seconds (15 minutes) while the maximum if 3600 (1 hour). Even if MFA is used with role based authentication, 3600 is still the maximum.
For MFA authentication by itself (using an IAM User or root user), the minimum is 900 seconds (15 minutes), the maximum is 129600 (36 hours).
The hosting region for AWS Cognito.
string
"NORTHERNVIRGINIA"
The hosting region for AWS Cognito. Available values are OHIO, NORTHERNVIRGINIA, NORTHERNCALIFORNIA, OREGON, CAPETOWN, HONGKONG, MUMBAI, OSAKA, SEOUL, SINGAPORE, SYDNEY, TOKYO, CENTRAL, BEIJING, NINGXIA, FRANKFURT, IRELAND, LONDON, MILAN, PARIS, STOCKHOLM, BAHRAIN, SAOPAULO, GOVCLOUDEAST, and GOVCLOUDWEST.
The User Pool Id.
string
""
You can find this in AWS Cognito -> Manage User Pools -> select your user pool -> General settings -> Pool Id.
The User Pool Client App Id.
string
""
You can find this in AWS Cognito -> Manage Identity Pools -> select your user pool -> General settings -> App clients -> App client Id.
Optional. The User Pool Client App Secret.
string
""
You can find this in AWS Cognito -> Manage Identity Pools -> select your user pool -> General settings -> App clients -> App client secret.
The Identity Pool Id.
string
""
You can find this in AWS Cognito -> Manage Identity Pools -> select your identity pool -> Edit identity pool -> Identity Pool Id
When activated, file uploads into Amazon S3 buckets will be server-side encrypted.
string
"OFF"
Server-side encryption is the encryption of data at its destination by the application or service that receives it. Amazon S3 encrypts your data at the object level as it writes it to disks in its data centers and decrypts it for you when you access it. Learn more: https://docs.aws.amazon.com/AmazonS3/latest/userguide/serv-side-encryption.html
This section provides a complete list of the SSO properties you can configure in the connection string for this provider.
| Property | Description |
| User | The IDP user used to authenticate the IDP via SSO. |
| Password | The password used to authenticate the IDP user via SSO. |
| SSOLoginURL | The identity provider's login URL. |
| SSOProperties | Additional properties required to connect to the identity provider in a semicolon-separated list. |
| SSOExchangeUrl | The url used for consuming the SAML response and exchanging it with Amazon S3 specific credentials. |
The IDP user used to authenticate the IDP via SSO.
string
""
Together with Password, this field is used to authenticate in SSO connections against the Amazon S3 server.
The password used to authenticate the IDP user via SSO.
string
""
The User and Password are together used in SSO connections to authenticate with the server.
The identity provider's login URL.
string
""
The identity provider's login URL.
Additional properties required to connect to the identity provider in a semicolon-separated list.
string
""
Additional properties required to connect to the identity provider in a semicolon-separated list. SSOProperties is used in conjunction with the the AWSRoleARN and AWSPrincipalARN. The following section provides an example using the OKTA identity provider.
Set the AuthScheme to ADFS. The following connection properties need to be set:
AuthScheme=ADFS; AWSRegion=Ireland; [email protected]; Password=CH8WerW121235647iCa6; SSOLoginURL='https://adfs.domain.com'; AWSRoleArn=arn:aws:iam::1234:role/ADFS_SSO; AWSPrincipalArn=arn:aws:iam::1234:saml-provider/ADFSProvider;
ADFS Integrated
To use the ADFS Integrated flow, specify the SSOLoginURL and leave the username and password empty.
Set the AuthScheme to Okta. The following connection properties are used to authenticate through Okta:
then you need to use combinations of SSOProperties input parameters to authenticate using Okta. Otherwise, you do not need to set any of these values.
In SSOProperties when required, set these input parameters:
Example connection string:
AuthScheme=Okta; AWSRegion=Ireland; [email protected]; Password=CH8WerW121235647iCa6; SSOLoginURL='https://cdata-us.okta.com/home/amazon_aws/0oa35m8arsAL5f5NrE6NdA356/272'; SSOProperties='ApiToken=01230GGG2ceAnm_tPAf4MhiMELXZ0L0N1pAYrO1VR-hGQSf;'; AWSRoleArn=arn:aws:iam::1234:role/Okta_SSO; AWSPrincipalARN=arn:aws:iam::1234:saml-provider/OktaProvider;
The url used for consuming the SAML response and exchanging it with Amazon S3 specific credentials.
string
""
The CData Cloud will use the url specified here to consume a SAML response and retrieve Amazon S3 specific credentials. The retrieved credentials are the final piece during the SSO connection that are used to communicate with Amazon S3.
This section provides a complete list of the SSL properties you can configure in the connection string for this provider.
| Property | Description |
| SSLServerCert | The certificate to be accepted from the server when connecting using TLS/SSL. |
The certificate to be accepted from the server when connecting using TLS/SSL.
string
""
If using a TLS/SSL connection, this property can be used to specify the TLS/SSL certificate to be accepted from the server. Any other certificate that is not trusted by the machine is rejected.
This property can take the following forms:
| Description | Example |
| A full PEM Certificate (example shortened for brevity) | -----BEGIN CERTIFICATE----- MIIChTCCAe4CAQAwDQYJKoZIhv......Qw== -----END CERTIFICATE----- |
| A path to a local file containing the certificate | C:\cert.cer |
| The public key (example shortened for brevity) | -----BEGIN RSA PUBLIC KEY----- MIGfMA0GCSq......AQAB -----END RSA PUBLIC KEY----- |
| The MD5 Thumbprint (hex values can also be either space or colon separated) | ecadbdda5a1529c58a1e9e09828d70e4 |
| The SHA1 Thumbprint (hex values can also be either space or colon separated) | 34a929226ae0819f2ec14b4a3d904f801cbb150d |
If not specified, any certificate trusted by the machine is accepted.
Use '*' to signify to accept all certificates. Note that this is not recommended due to security concerns.
This section provides a complete list of the Firewall properties you can configure in the connection string for this provider.
| Property | Description |
| FirewallType | The protocol used by a proxy-based firewall. |
| FirewallServer | The name or IP address of a proxy-based firewall. |
| FirewallPort | The TCP port for a proxy-based firewall. |
| FirewallUser | The user name to use to authenticate with a proxy-based firewall. |
| FirewallPassword | A password used to authenticate to a proxy-based firewall. |
The protocol used by a proxy-based firewall.
string
"NONE"
This property specifies the protocol that the Cloud will use to tunnel traffic through the FirewallServer proxy. Note that by default, the Cloud connects to the system proxy; to disable this behavior and connect to one of the following proxy types, set ProxyAutoDetect to false.
| Type | Default Port | Description |
| TUNNEL | 80 | When this is set, the Cloud opens a connection to Amazon S3 and traffic flows back and forth through the proxy. |
| SOCKS4 | 1080 | When this is set, the Cloud sends data through the SOCKS 4 proxy specified by FirewallServer and FirewallPort and passes the FirewallUser value to the proxy, which determines if the connection request should be granted. |
| SOCKS5 | 1080 | When this is set, the Cloud sends data through the SOCKS 5 proxy specified by FirewallServer and FirewallPort. If your proxy requires authentication, set FirewallUser and FirewallPassword to credentials the proxy recognizes. |
To connect to HTTP proxies, use ProxyServer and ProxyPort. To authenticate to HTTP proxies, use ProxyAuthScheme, ProxyUser, and ProxyPassword.
The name or IP address of a proxy-based firewall.
string
""
This property specifies the IP address, DNS name, or host name of a proxy allowing traversal of a firewall. The protocol is specified by FirewallType: Use FirewallServer with this property to connect through SOCKS or do tunneling. Use ProxyServer to connect to an HTTP proxy.
Note that the Cloud uses the system proxy by default. To use a different proxy, set ProxyAutoDetect to false.
The TCP port for a proxy-based firewall.
int
0
This specifies the TCP port for a proxy allowing traversal of a firewall. Use FirewallServer to specify the name or IP address. Specify the protocol with FirewallType.
The user name to use to authenticate with a proxy-based firewall.
string
""
The FirewallUser and FirewallPassword properties are used to authenticate against the proxy specified in FirewallServer and FirewallPort, following the authentication method specified in FirewallType.
A password used to authenticate to a proxy-based firewall.
string
""
This property is passed to the proxy specified by FirewallServer and FirewallPort, following the authentication method specified by FirewallType.
This section provides a complete list of the Proxy properties you can configure in the connection string for this provider.
| Property | Description |
| ProxyAutoDetect | This indicates whether to use the system proxy settings or not. This takes precedence over other proxy settings, so you'll need to set ProxyAutoDetect to FALSE in order use custom proxy settings. |
| ProxyServer | The hostname or IP address of a proxy to route HTTP traffic through. |
| ProxyPort | The TCP port the ProxyServer proxy is running on. |
| ProxyAuthScheme | The authentication type to use to authenticate to the ProxyServer proxy. |
| ProxyUser | A user name to be used to authenticate to the ProxyServer proxy. |
| ProxyPassword | A password to be used to authenticate to the ProxyServer proxy. |
| ProxySSLType | The SSL type to use when connecting to the ProxyServer proxy. |
| ProxyExceptions | A semicolon separated list of destination hostnames or IPs that are exempt from connecting through the ProxyServer . |
This indicates whether to use the system proxy settings or not. This takes precedence over other proxy settings, so you'll need to set ProxyAutoDetect to FALSE in order use custom proxy settings.
bool
true
This takes precedence over other proxy settings, so you'll need to set ProxyAutoDetect to FALSE in order use custom proxy settings.
To connect to an HTTP proxy, see ProxyServer. For other proxies, such as SOCKS or tunneling, see FirewallType.
The hostname or IP address of a proxy to route HTTP traffic through.
string
""
The hostname or IP address of a proxy to route HTTP traffic through. The Cloud can use the HTTP, Windows (NTLM), or Kerberos authentication types to authenticate to an HTTP proxy.
If you need to connect through a SOCKS proxy or tunnel the connection, see FirewallType.
By default, the Cloud uses the system proxy. If you need to use another proxy, set ProxyAutoDetect to false.
The TCP port the ProxyServer proxy is running on.
int
80
The port the HTTP proxy is running on that you want to redirect HTTP traffic through. Specify the HTTP proxy in ProxyServer. For other proxy types, see FirewallType.
The authentication type to use to authenticate to the ProxyServer proxy.
string
"BASIC"
This value specifies the authentication type to use to authenticate to the HTTP proxy specified by ProxyServer and ProxyPort.
Note that the Cloud will use the system proxy settings by default, without further configuration needed; if you want to connect to another proxy, you will need to set ProxyAutoDetect to false, in addition to ProxyServer and ProxyPort. To authenticate, set ProxyAuthScheme and set ProxyUser and ProxyPassword, if needed.
The authentication type can be one of the following:
If you need to use another authentication type, such as SOCKS 5 authentication, see FirewallType.
A user name to be used to authenticate to the ProxyServer proxy.
string
""
The ProxyUser and ProxyPassword options are used to connect and authenticate against the HTTP proxy specified in ProxyServer.
You can select one of the available authentication types in ProxyAuthScheme. If you are using HTTP authentication, set this to the user name of a user recognized by the HTTP proxy. If you are using Windows or Kerberos authentication, set this property to a user name in one of the following formats:
user@domain domain\user
A password to be used to authenticate to the ProxyServer proxy.
string
""
This property is used to authenticate to an HTTP proxy server that supports NTLM (Windows), Kerberos, or HTTP authentication. To specify the HTTP proxy, you can set ProxyServer and ProxyPort. To specify the authentication type, set ProxyAuthScheme.
If you are using HTTP authentication, additionally set ProxyUser and ProxyPassword to HTTP proxy.
If you are using NTLM authentication, set ProxyUser and ProxyPassword to your Windows password. You may also need these to complete Kerberos authentication.
For SOCKS 5 authentication or tunneling, see FirewallType.
By default, the Cloud uses the system proxy. If you want to connect to another proxy, set ProxyAutoDetect to false.
The SSL type to use when connecting to the ProxyServer proxy.
string
"AUTO"
This property determines when to use SSL for the connection to an HTTP proxy specified by ProxyServer. This value can be AUTO, ALWAYS, NEVER, or TUNNEL. The applicable values are the following:
| AUTO | Default setting. If the URL is an HTTPS URL, the Cloud will use the TUNNEL option. If the URL is an HTTP URL, the component will use the NEVER option. |
| ALWAYS | The connection is always SSL enabled. |
| NEVER | The connection is not SSL enabled. |
| TUNNEL | The connection is through a tunneling proxy. The proxy server opens a connection to the remote host and traffic flows back and forth through the proxy. |
A semicolon separated list of destination hostnames or IPs that are exempt from connecting through the ProxyServer .
string
""
The ProxyServer is used for all addresses, except for addresses defined in this property. Use semicolons to separate entries.
Note that the Cloud uses the system proxy settings by default, without further configuration needed; if you want to explicitly configure proxy exceptions for this connection, you need to set ProxyAutoDetect = false, and configure ProxyServer and ProxyPort. To authenticate, set ProxyAuthScheme and set ProxyUser and ProxyPassword, if needed.
This section provides a complete list of the Logging properties you can configure in the connection string for this provider.
| Property | Description |
| Logfile | A filepath which designates the name and location of the log file. |
| Verbosity | The verbosity level that determines the amount of detail included in the log file. |
| LogModules | Core modules to be included in the log file. |
| MaxLogFileSize | A string specifying the maximum size in bytes for a log file (for example, 10 MB). |
| MaxLogFileCount | A string specifying the maximum file count of log files. |
A filepath which designates the name and location of the log file.
string
""
Once this property is set, the Cloud will populate the log file as it carries out various tasks, such as when authentication is performed or queries are executed. If the specified file doesn't already exist, it will be created.
Connection strings and version information are also logged, though connection properties containing sensitive information are masked automatically.
If a relative filepath is supplied, the location of the log file will be resolved based on the path found in the Location connection property.
For more control over what is written to the log file, you can adjust the Verbosity property.
Log contents are categorized into several modules. You can show/hide individual modules using the LogModules property.
To edit the maximum size of a single logfile before a new one is created, see MaxLogFileSize.
If you would like to place a cap on the number of logfiles generated, use MaxLogFileCount.
The verbosity level that determines the amount of detail included in the log file.
string
"1"
The verbosity level determines the amount of detail that the Cloud reports to the Logfile. Verbosity levels from 1 to 5 are supported. These are detailed in the Logging page.
Core modules to be included in the log file.
string
""
Only the modules specified (separated by ';') will be included in the log file. By default all modules are included.
See the Logging page for an overview.
A string specifying the maximum size in bytes for a log file (for example, 10 MB).
string
"100MB"
When the limit is hit, a new log is created in the same folder with the date and time appended to the end. The default limit is 100 MB. Values lower than 100 kB will use 100 kB as the value instead.
Adjust the maximum number of logfiles generated with MaxLogFileCount.
A string specifying the maximum file count of log files.
int
-1
When the limit is hit, a new log is created in the same folder with the date and time appended to the end and the oldest log file will be deleted.
The minimum supported value is 2. A value of 0 or a negative value indicates no limit on the count.
Adjust the maximum size of the logfiles generated with MaxLogFileSize.
This section provides a complete list of the Schema properties you can configure in the connection string for this provider.
| Property | Description |
| Location | A path to the directory that contains the schema files defining tables, views, and stored procedures. |
| BrowsableSchemas | This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC. |
| Tables | This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC. |
| Views | Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC. |
A path to the directory that contains the schema files defining tables, views, and stored procedures.
string
"%APPDATA%\\CData\\AmazonS3 Data Provider\\Schema"
The path to a directory which contains the schema files for the Cloud (.rsd files for tables and views, .rsb files for stored procedures). The folder location can be a relative path from the location of the executable. The Location property is only needed if you want to customize definitions (for example, change a column name, ignore a column, and so on) or extend the data model with new tables, views, or stored procedures.
If left unspecified, the default location is "%APPDATA%\\CData\\AmazonS3 Data Provider\\Schema" with %APPDATA% being set to the user's configuration directory:
This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.
string
""
Listing the schemas from databases can be expensive. Providing a list of schemas in the connection string improves the performance.
This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC.
string
""
Listing the tables from some databases can be expensive. Providing a list of tables in the connection string improves the performance of the Cloud.
This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.
Specify the tables you want in a comma-separated list. Each table should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Tables=TableA,[TableB/WithSlash],WithCatalog.WithSchema.`TableC With Space`.
Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.
Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC.
string
""
Listing the views from some databases can be expensive. Providing a list of views in the connection string improves the performance of the Cloud.
This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.
Specify the views you want in a comma-separated list. Each view should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Views=ViewA,[ViewB/WithSlash],WithCatalog.WithSchema.`ViewC With Space`.
Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.
This section provides a complete list of the Caching properties you can configure in the connection string for this provider.
| Property | Description |
| AutoCache | Automatically caches the results of SELECT queries into a cache database specified by either CacheLocation or both of CacheConnection and CacheProvider . |
| CacheLocation | Specifies the path to the cache when caching to a file. |
| CacheTolerance | The tolerance for stale data in the cache specified in seconds when using AutoCache . |
| Offline | Use offline mode to get the data from the cache instead of the live source. |
| CacheMetadata | This property determines whether or not to cache the table metadata to a file store. |
Automatically caches the results of SELECT queries into a cache database specified by either CacheLocation or both of CacheConnection and CacheProvider .
bool
false
When AutoCache = true, the Cloud automatically maintains a cache of your table's data in the database of your choice.
When AutoCache = true, the Cloud caches to a simple, file-based cache. You can configure its location or cache to a different database with the following properties:
Specifies the path to the cache when caching to a file.
string
"%APPDATA%\\CData\\AmazonS3 Data Provider"
The CacheLocation is a simple, file-based cache.
If left unspecified, the default location is "%APPDATA%\\CData\\AmazonS3 Data Provider" with %APPDATA% being set to the user's configuration directory:
The tolerance for stale data in the cache specified in seconds when using AutoCache .
int
600
The tolerance for stale data in the cache specified in seconds. This only applies when AutoCache is used. The Cloud checks with the data source for newer records after the tolerance interval has expired. Otherwise, it returns the data directly from the cache.
Use offline mode to get the data from the cache instead of the live source.
bool
false
When Offline = true, all queries execute against the cache as opposed to the live data source. In this mode, certain queries like INSERT, UPDATE, DELETE, and CACHE are not allowed.
This property determines whether or not to cache the table metadata to a file store.
bool
false
As you execute queries with this property set, table metadata in the Amazon S3 catalog are cached to the file store specified by CacheLocation if set or the user's home directory otherwise. A table's metadata will be retrieved only once, when the table is queried for the first time.
The Cloud automatically persists metadata in memory for up to two hours when you first discover the metadata for a table or view and therefore, CacheMetadata is generally not required. CacheMetadata becomes useful when metadata operations are expensive such as when you are working with large amounts of metadata or when you have many short-lived connections.
This section provides a complete list of the Miscellaneous properties you can configure in the connection string for this provider.
| Property | Description |
| CreateFoldersOnDownload | Automatically create the destination folder during execution of DownloadObjects Stored Procedure. |
| EncodeFilename | Determines if the local file path in DownloadObjects Stored Procedure should be encoded. |
| MaxRows | Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time. |
| MaxThreads | Specifies the number of concurrent requests. |
| Other | These hidden properties are used only in specific use cases. |
| PseudoColumns | This property indicates whether or not to include pseudo columns as columns to the table. |
| RTK | The runtime key used for licensing. |
| Timeout | The value in seconds until the timeout error is thrown, canceling the operation. |
| UserDefinedViews | A filepath pointing to the JSON configuration file containing your custom views. |
Automatically create the destination folder during execution of DownloadObjects Stored Procedure.
bool
false
This connection property will work only if EncodeFilename is set to false. If CreateFoldersOnDownload is set to true it will automatically create the folder in which the file is going to be downloaded.
Determines if the local file path in DownloadObjects Stored Procedure should be encoded.
bool
true
Determines if the local file path in DownloadObjects Stored Procedure should be encoded. Ex.
In the following query:
EXECUTE DownloadObjects
Object='SBatch100k_ORDERS_cdata_replicate_temporary_table/20201106023735184_0.csv',
Bucket='actiantest',
LocalFolderPath='C:\Users\User\Desktop\amazons3test'
The final destination of the file will be C:/Users/User/Desktop/amazons3test/SBatch100k_ORDERS_cdata_replicate_temporary_table%2F20201106023735184_0.csv if EncodeFilename=true
and C:/Users/User/Desktop/amazons3test/SBatch100k_ORDERS_cdata_replicate_temporary_table/20201106023735184_0.csv if EncodeFilename=false.
In addition you can use CreateFoldersOnDownload connection property if and only if EncodeFilename=false. If CreateFoldersOnDownload is set to true
it will automatically create the folder in which the file is going to be downloaded.
Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.
int
-1
Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.
Specifies the number of concurrent requests.
string
"5"
This property allows you to issue multiple requests simultaneously, thereby improving performance.
These hidden properties are used only in specific use cases.
string
""
The properties listed below are available for specific use cases. Normal driver use cases and functionality should not require these properties.
Specify multiple properties in a semicolon-separated list.
| DefaultColumnSize | Sets the default length of string fields when the data source does not provide column length in the metadata. The default value is 2000. |
| ConvertDateTimeToGMT | Determines whether to convert date-time values to GMT, instead of the local time of the machine. |
| RecordToFile=filename | Records the underlying socket data transfer to the specified file. |
This property indicates whether or not to include pseudo columns as columns to the table.
string
""
This setting is particularly helpful in Entity Framework, which does not allow you to set a value for a pseudo column unless it is a table column. The value of this connection setting is of the format "Table1=Column1, Table1=Column2, Table2=Column3". You can use the "*" character to include all tables and all columns; for example, "*=*".
The runtime key used for licensing.
string
""
The RTK property may be used to license a build.
The value in seconds until the timeout error is thrown, canceling the operation.
int
60
If Timeout = 0, operations do not time out. The operations run until they complete successfully or until they encounter an error condition.
If Timeout expires and the operation is not yet complete, the Cloud throws an exception.
A filepath pointing to the JSON configuration file containing your custom views.
string
""
User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json. The Cloud automatically detects the views specified in this file.
You can also have multiple view definitions and control them using the UserDefinedViews connection property. When you use this property, only the specified views are seen by the Cloud.
This User Defined View configuration file is formatted as follows:
For example:
{
"MyView": {
"query": "SELECT * FROM Buckets WHERE MyColumn = 'value'"
},
"MyView2": {
"query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
}
}
Use the UserDefinedViews connection property to specify the location of your JSON configuration file. For example:
"UserDefinedViews", "C:\\Users\\yourusername\\Desktop\\tmp\\UserDefinedViews.json"