CData Cloud offers access to Shopify 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 Shopify through CData Cloud.
CData Cloud allows you to standardize and configure connections to Shopify as though it were any other OData endpoint, or standard SQL Server/MySQL database.
This page provides a guide to Establishing a Connection to Shopify 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 Shopify and configure any necessary connection properties to create a database in CData Cloud
Accessing data from Shopify through the available standard services and CData Cloud administration is documented in further details in the CData Cloud Documentation.
Connect to Shopify 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.
To connect using OAuth, you must create a public or custom application in the Shopify Partner Dashboard. During this process, API key and API secret key values are generated. You need these keys when you connect using a custom application. See Creating a Custom OAuth App for the procedure.
Note: You must set AuthScheme to OAuth. All the sections below assume that you have done so. Otherwise, you cannot connect.
You must create a custom application and specify Client Id and Client Secret properties.
After setting the following, you are ready to connect:
Create a Custom OAuth App
See Creating a Custom OAuth App for the procedure. You can then follow the steps below to authenticate and connect to data.
Obtain a Verifier Code
Set the following properties on the headless machine:
You can then follow the steps below to authenticate from another machine and obtain the OAuthVerifier connection property.
On the headless machine, set the following connection properties to obtain the OAuth authentication values:
After the OAuth settings file is generated, set the following properties to connect to data:
Transfer OAuth Settings
Follow the steps below to install the Cloud on another machine, authenticate, and then transfer the resulting OAuth values.
On a second machine, install the Cloud and connect with the following properties set:
Test the connection to authenticate. The resulting authentication values are encryped and writtento the path specified by OAuthSettingsLocation. After you have successfully tested the connection, copy the OAuth settings file to your headless machine. On the headless machine, set the following connection properties to connect to data:
You have to create a custom OAuth app to change the information displayed when users log into the Shopify OAuth endpoint to grant permissions to the Cloud.
GraphQL was created to overcome shortcomings that REST APIs were not designed to address. Working with GraphQL has a number of advantages, one of which being the reduced number of HTTP calls required to obtain all of the data available through REST API.
However, calls to the GraphQL Admin API are limited based on estimated query costs, which means that the cost of queries over time should be considered rather than the amount of requests.
The points function in a very straightforward manner. A bucket of 1000 cost points is given to each app and store combination, with a leak rate of 50 cost points per second. This means that at any given time, the total cost of your queries cannot exceed 1,000 points, and that space in the app's bucket is created at a rate of 50 points per second. Plainly put, every second, you're given 50 points in a typical plan. Any mutation that requires you to edit, create, or delete data costs ten points. The cost of obtaining an object, on the other hand, is merely one point. Let's imagine you needed an order, but you also wanted each and every line item from that order. That isn't going to be a one-point penalty. If your order contains 10 line items, each of those line items will cost one point, and the order itself will cost one point, totaling 11 points.
The requested and actual query costs are combined to set the limit. Before the query can be executed, the app's bucket must have enough space to accommodate the desired cost. When the query is finished, the bucket is repaid the difference between the requested and actual query costs.
The cost of the request and the state of the throttle are included in the response. The following information is returned under the extensions key:
"extensions": {
"cost": {
"requestedQueryCost": 101,
"actualQueryCost": 46,
"throttleStatus": {
"maximumAvailable": 1000,
"currentlyAvailable": 954,
"restoreRate": 50
}
}
}
Taking these into consideration, our driver must calculate the appropriate quantity of data that can be retrieved each request to avoid throttling. The pagesize of a table is calculated automatically based on the number of fields and data in the table to conform to GraphQL API restrictions. Due to a low computed pagesize, performance for large tables in GraphQL may be lacking. An alternative is to increase the MaxPointsPerCall, which forces an increase in the pageisze, but we do not encourage this as it will most certainly result in throttling.
Bulk operations, rather than single queries, are recommended to be used to query and get massive amounts of data.
Bulk operations are intended to handle massive amounts of data and do not have the same cost or rate constraints as single queries. You'll have to paginate your data sets if you don't utilize the bulk query. For REST, for example, a response can only include 250 elements. GraphQL is cost-based, but you're still limited to a specific number per request.
With a Bulk Operation API, this is not the case. Therefore, when using the GraphQL schema, we suggest setting UseBulkAPI to TRUE in the connection string to retrieve massive amounts of data without worrying about pagination or throttling.
Date | Build Number | Change Type | Description |
2/28/2023 | 8459 | Shopify | Added
|
01/04/2023 | 8404 | Shopify | Added
|
12/14/2022 | 8383 | General | Changed
|
11/25/2022 | 8364 | Shopify | Added
Changed
|
12/7/2022 | 8347 | Shopify | Added
Changed
Removed
|
11/7/2022 | 8346 | Shopify | Changed
Removed
|
09/30/2022 | 8308 | General | Changed
|
08/29/2022 | 8276 | Shopify | Added
Changed
|
08/17/2022 | 8264 | General | Changed
|
08/03/2022 | 8250 | Shopify | Removed
|
07/21/2022 | 8237 | Shopify | Added
|
06/13/2022 | 8199 | Shopify | Deprecated
|
05/19/2022 | 8174 | Shopify | Added
|
05/05/2022 | 8160 | Shopify | Added
|
04/25/2022 | 8150 | Shopify | Added
|
04/15/2022 | 8140 | Shopify | Changed
|
03/04/2022 | 8098 | Shopify | Changed
|
02/28/2022 | 8094 | Shopify | Changed
|
02/02/2022 | 8068 | Shopify | Changed
|
12/08/2021 | 8012 | Shopify | Added
|
11/09/2021 | 7983 | Shopify | Added
|
10/29/2021 | 7972 | Shopify | Changed
Removed
|
09/13/2021 | 7926 | Shopify | Changed
|
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/08/2021 | 7859 | General | Added
|
06/26/2021 | 7847 | Shopify | Added
|
06/25/2021 | 7846 | Shopify | Added
|
06/10/2021 | 7831 | Shopify | Added
Changed
|
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
|
03/10/2021 | 7739 | Shopify | Added
|
This section details a selection of advanced features of the Shopify 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 Shopify 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 Customers 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 Shopify 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 Shopify Query Evaluation component examines SQL queries and returns information indicating what parts of the query the Cloud is not capable of executing natively.
The Shopify 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.
The CData Cloud supports several operations on data, including querying, deleting, modifying, and inserting.
See SELECT Statements for a syntax reference and examples.
See Data Model for information on the capabilities of the Shopify API.
See INSERT Statements for a syntax reference and examples, as well as retrieving the new records' Ids.
The primary key Id is required to update a record. See UPDATE Statements for a syntax reference and examples.
The primary key Id is required to delete a record. See DELETE Statements for a syntax reference and examples.
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>
]
]
} | SCOPE_IDENTITY()
<expression> ::=
| <column_reference>
| @ <parameter>
| ?
| COUNT( * | { [ DISTINCT ] <expression> } )
| { AVG | MAX | MIN | SUM | COUNT } ( <expression> )
| NULLIF ( <expression> , <expression> )
| COALESCE ( <expression> , ... )
| CASE <expression>
WHEN { <expression> | <search_condition> } THEN { <expression> | NULL } [ ... ]
[ ELSE { <expression> | NULL } ]
END
| <literal>
| <sql_function>
<search_condition> ::=
{
<expression> { = | > | < | >= | <= | <> | != | LIKE | NOT LIKE | IN | NOT IN | IS NULL | IS NOT NULL | AND | OR | CONTAINS | BETWEEN } [ <expression> ]
} [ { AND | OR } ... ]
SELECT * FROM Customers
SELECT [Id] AS MY_Id FROM Customers
SELECT CAST(Size AS VARCHAR) AS Str_Size FROM Customers
SELECT * FROM Customers WHERE FirstName = 'jdoe1234'
SELECT COUNT(*) AS MyCount FROM Customers
SELECT COUNT(DISTINCT Id) FROM Customers
SELECT DISTINCT Id FROM Customers
SELECT Id, MAX(Size) FROM Customers GROUP BY IdSee Aggregate Functions for details.
SELECT Customers.MultipassIdentifier, Orders.TotalPrice FROM Customers, Orders WHERE Customers.Id=Orders.CustomerIdSee JOIN Queries for details.
SELECT FirstName, Id FROM Customers ORDER BY Id ASC
SELECT FirstName, Id FROM Customers LIMIT 10
SELECT * FROM Customers WHERE FirstName = @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 Shopify.
SELECT * FROM Customers WHERE Pseudo = '@Pseudo'
Returns the number of rows matching the query criteria.
SELECT COUNT(*) FROM Customers WHERE FirstName = 'jdoe1234'
Returns the number of distinct, non-null field values matching the query criteria.
SELECT COUNT(DISTINCT FirstName) AS DistinctValues FROM Customers WHERE FirstName = 'jdoe1234'
Returns the average of the column values.
SELECT Id, AVG(Size) FROM Customers WHERE FirstName = 'jdoe1234' GROUP BY Id
Returns the minimum column value.
SELECT MIN(Size), Id FROM Customers WHERE FirstName = 'jdoe1234' GROUP BY Id
Returns the maximum column value.
SELECT Id, MAX(Size) FROM Customers WHERE FirstName = 'jdoe1234' GROUP BY Id
Returns the total sum of the column values.
SELECT SUM(Size) FROM Customers WHERE FirstName = 'jdoe1234'
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.MultipassIdentifier, Orders.TotalPrice FROM Customers, Orders WHERE Customers.Id=Orders.CustomerId
A left join selects all rows in the FROM table and only matching rows in the JOIN table:
SELECT Customers.MultipassIdentifier, Orders.TotalPrice FROM Customers LEFT OUTER JOIN Orders ON Customers.Id=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 FirstName, Id INTO [csv://Customers.txt] FROM [Customers] WHERE FirstName = 'jdoe1234'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 FirstName, Id INTO [csv://Customers.txt;delimiter=tab] FROM [Customers] WHERE FirstName = 'jdoe1234'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 Customers WHERE FirstName = 'jdoe1234'
These functions perform date and date time manipulations. See DATE Functions for more details.
SELECT CURRENT_TIMESTAMP() FROM Customers
These functions provide mathematical operations. See MATH Functions for more details.
SELECT RAND() FROM Customers
SELECT CONCAT('Mr.', SPACE(2), firstname, SPACE(4), lastname) FROM Customers
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 create new records, use INSERT statements.
The INSERT statement specifies the columns to be inserted and the new column values. You can specify the column values in a comma-separated list in the VALUES clause, as shown in the following example:
INSERT INTO <table_name>
( <column_reference> [ , ... ] )
VALUES
( { <expression> | NULL } [ , ... ] )
<expression> ::=
| @ <parameter>
| ?
| <literal>
The following is an example query:
INSERT INTO Customers (Id) VALUES ('3478365783')
To modify existing records, use UPDATE statements.
The UPDATE statement takes as input a comma-separated list of columns and new column values as name-value pairs in the SET clause, as shown in the following example:
UPDATE <table_name> SET { <column_reference> = <expression> } [ , ... ] WHERE { Id = <expression> } [ { AND | OR } ... ]
<expression> ::=
| @ <parameter>
| ?
| <literal>
The following is an example query:
UPDATE Customers SET Id='3478365783' WHERE Id = @myId
To delete information from a table, use DELETE statements.
The DELETE statement requires the table name in the FROM clause and the row's primary key in the WHERE clause, as shown in the following example:
<delete_statement> ::= DELETE FROM <table_name> WHERE { Id = <expression> } [ { AND | OR } ... ]
<expression> ::=
| @ <parameter>
| ?
| <literal>
The following is an example query:
DELETE FROM Customers WHERE Id = @myId
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)
The CData Cloud models entities in the Shopify API as tables, views, and stored procedures. These are defined in schema files, which are simple, text-based configuration files that are easy to customize.
See REST Data Model for the available entities in the REST API.
See GRAPHQL Data Model for the available entities in the GraphQL API.
The CData Cloud models the Shopify API as relational views, and stored procedures.
To use REST Data Model, simply set Schema to REST.
Tables are tables that can be modified, such as Orders, Products.
Views are tables that cannot be modified, such as Events, Patouts, Reports. Typically, model data that is read-only and cannot be updated are shown as views.
Stored Procedures are function-like interfaces to the data source. They can be used to search, update, and modify information in the data source.
The Cloud models the data in Shopify into a list of tables that can be queried using standard SQL statements.
Generally, querying Shopify 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 |
ApplicationCharges | Create or view Application Charges for Billing. |
ApplicationCredit | Create or view Application Credit for Billing. |
Articles | Create, read, update or delete articles |
Assets | Create, read, update or delete assets |
Blogs | Create, read, update or delete blogs |
CollectionListings | Query and delete information regarding different collects. |
Collects | Query, insert, or delete information regarding different collects. |
Comments | Create, read, update and delete the comments. |
Countries | Create, select, update, and delete information regarding countries. |
CustomCollections | Query, insert, update, or delete information regarding different custom collections. |
CustomerAddresses | Create, update, delete, and query customer addresses. |
Customers | Create, update, delete, and query customers. |
DiscountCodes | Create, select, update, and delete information regarding discount codes. |
DraftOrders | Create, update, delete, and query draft orders. |
FulfillmentEvents | Create, delete, and query information regarding fulfillment events. |
Fulfillments | Create, update, and query fulfillments. |
FulfillmentServices | Query, create, update, and delete information regarding different fulfillment services. |
InventoryItems | Query and update information regarding different inventory items. |
InventoryLevels | Query, create, and update information regarding different inventory levels. |
MarketingEvents | Create, update, delete, and query marketing events. |
Metafields | Retrieves a list of metafields that belong to a resource. |
OrderRisks | Create, update, delete, and query order risks. |
Orders | Create, update, delete, and query orders. |
OrderTransactions | Create and query transactions. |
Pages | Create, read, update or delete pages |
PriceRules | Create, update, delete, and query price rules. |
ProductImages | Query, Update and Delete Product Images |
ProductListings | Query and delete product listings. |
Products | Create, update, delete, and query products. |
ProductVariants | Query and delete product listings. |
Provinces | Query and update information regarding different provinces. |
RecurringApplicationCharges | Create, update, delete, and query Recurring Application Charges. |
Redirects | Create, read, update or delete redirects. |
Refunds | Create and query refunds. |
ScriptTags | Create, read, update or delete script tags. |
SmartCollections | Query, insert, update, or delete information regarding different smart collections. |
Themes | Create, read, update or delete themes |
UsageCharges | Create or view Usage Charges for Recurring Application Charges. |
Create or view Application Charges for Billing.
The Cloud processes all filters client-side within the Cloud. The following queries are the only ones processed server side:
SELECT * FROM ApplicationCharges
SELECT * FROM ApplicationCharges WHERE Id='123'
You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.
INSERT INTO ApplicationCharges ( Test ) VALUES ( 'true' )
Name | Type | ReadOnly | References | Description |
Id [KEY] | Long | False |
The Unique Identifier of the Application Charge | |
Name | String | False |
The Order Number | |
ApiClientId | Long | False |
The API Client ID | |
Price | Decimal | False |
The price of the application charge | |
Status | String | False |
The status of the application charge. Valid Values are: pending, active, declined, expired | |
ReturnUrl | String | False |
The URL where the merchant is redirected after accepting a charge. | |
Test | String | False |
Whether the application charge is a test transaction. Valid values:true,null | |
CreatedAt | Datetime | False |
The date and time (ISO 8601 format) when the application charge was created. | |
UpdatedAt | Datetime | False |
The date and time (ISO 8601 format) when the application charge was last updated. | |
ChargeType | String | False |
The type of the application charge | |
DecoratedReturnUrl | String | False |
The decorated return url |
Create or view Application Credit for Billing.
The Cloud processes all filters client-side within the Cloud. The following queries are the only ones processed server side:
SELECT * FROM ApplicationCredit
SELECT * FROM ApplicationCredit WHERE Id='123'
You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.
Insert Into ApplicationCredit (Amount, Description, Test) VALUES ('100', 'succes or failure', 'true')
Name | Type | ReadOnly | References | Description |
Id [KEY] | Long | False |
The ID of the application credit. | |
Amount | Decimal | False |
The amount refunded by the application credit. | |
Description | String | False |
The description of the application credit. | |
Test | String | False |
Whether the application credit is a test transaction. Valid values: true,null |
Create, read, update or delete articles
The Cloud processes all filters client-side within the Cloud. The following queries are the only ones processed server side:
SELECT * FROM Articles
SELECT * FROM Articles WHERE Id='123'
You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.
You must specify the Title to create an Article.
INSERT INTO Articles(BlogId,Author,Title) VALUES ('599421749','Adam','New Article')
You must specify the blog id and id to update an article. For example:
UPDATE Articles SET Author='Harry' WHERE BlogId = '599421749' AND Id = '5557081211'
You must specify the Id and the Blog Id of the article to delete it.
DELETE FROM Articles WHERE BlogId = '599421749' AND Id = '5556952105'
Name | Type | ReadOnly | References | Description |
Id [KEY] | Long | True |
The ID of the article. | |
BlogId [KEY] | Long | False |
Blogs.Id |
The ID of the blog containing the article. |
AdminGraphqlApiId | String | True |
Displays the Admin Graphql API id. | |
Author | String | False |
The name of the author of the article. | |
BodyHtml | String | False |
The text of the body of the article, complete with HTML markup. | |
CreatedAt | Datetime | True |
The date and time (ISO 8601 format) when the article was created. | |
Handle | String | False |
A human-friendly unique string for the article that's automatically generated from the article's title. The handle is used in the article's URL. | |
Image.alt | String | False |
Alternative text that describes the image. | |
Image.createdAt | Datetime | True |
The date and time when image is created. | |
Image.height | Integer | False |
Height of the image. | |
Image.src | String | False |
A source URL that specifies the location of the image. | |
Image.width | Integer | False |
Width of the image. | |
Image.attachment | String | False |
An image attached to article returned as Base64-encoded binary data. | |
PublishedAt | Datetime | False |
The date and time (ISO 8601 format) when the article was published. | |
SummaryHtml | String | False |
A summary of the article, complete with HTML markup. | |
Tags | String | False |
Tags are additional short descriptors formatted as a string of comma-separated values. | |
Template_suffix | String | False |
The name of the template an article is using if it's using an alternate template. If an article is using the default article.liquid template, then the value returned is null. | |
Title | String | False |
The title of the article. | |
UpdatedAt | Datetime | True |
The date and time (ISO 8601 format) when the article was last updated. | |
UserId | Long | True |
A unique numeric identifier for the author of the article. |
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
Name | Type | Description |
published_status | String |
Filter articles by their publish status. Valid values are: published, unpublished and any ; default: any. |
Create, read, update or delete assets
The Cloud processes all filters client-side within the Cloud. The following queries are the only ones processed server side:
SELECT * FROM Assets
SELECT * FROM Assets WHERE Key='assets/abc.gif'
You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.
You must specify the Theme Id and Key to update an Asset. For example:
UPDATE Assets SET value='pqrs' WHERE Key = 'assets/abc.gif' AND ThemeId = '282977'
You must specify the Id and the Theme Id of the Asset to delete it.
DELETE FROM Assets WHERE Key = 'assets/abc.gif' AND ThemeId = '282977'
Name | Type | ReadOnly | References | Description |
Key [KEY] | String | False |
The path to the asset within a theme. | |
ThemeId | Long | True |
Themes.Id |
The ID for the theme that an asset belongs to. |
Attachment | String | False |
A base64-encoded image. | |
Checksum | String | True |
The MD5 representation of the content, consisting of a string of 32 hexadecimal digits. May be null if an asset has not been updated recently. | |
ContentType | String | True |
The MIME representation of the content, consisting of the type and subtype of the asset. | |
CreatedAt | Datetime | True |
The date and time (ISO 8601 format) when the asset was created. | |
PublicUrl | String | True |
The public-facing URL of the asset. | |
Size | Integer | True |
The asset size in bytes. | |
UpdatedAt | Datetime | True |
The date and time (ISO 8601 format) when an asset was last updated. | |
Value | String | False |
The text content of the asset, such as the HTML and Liquid markup of a template file. |
Create, read, update or delete blogs
The Cloud processes all filters client-side within the Cloud. The following queries are the only ones processed server side:
SELECT * FROM Blogs
SELECT * FROM Blogs WHERE Id='123'
You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.
You must specify the Title to create a Blog.
INSERT INTO Blogs(title) VALUES ('new blog')
You must specify the id to update a Blog. For example:
UPDATE Blogs SET title='new blog' WHERE Id = '77171130'
You must specify the Id of the Blog to delete it.
DELETE FROM Blogs WHERE Id = '555695'
Name | Type | ReadOnly | References | Description |
Id [KEY] | Long | True |
A unique numeric identifier for the blog. | |
AdminGraphqlApiId | String | True |
Displays the Admin Graphql API id. | |
Commentable | String | False |
Indicates whether readers can post comments to the blog and if comments are moderated or not. The allowed values are no, yes, moderate. The default value is no. | |
CreatedAt | Datetime | True |
The date and time when the blog was created. | |
Feedburner | String | False |
Feedburner is a web feed management provider and can be enabled to provide custom RSS feeds for Shopify bloggers. This property will default to blank or null unless feedburner is enabled through the shop admin. | |
FeedburnerLocation | String | False |
URL to the feedburner location for blogs that have enabled feedburner through their store admin. This property will default to blank or null unless feedburner is enabled through the shop admin. | |
Handle | String | False |
A human-friendly unique string for a blog automatically generated from its title. | |
Metafields | String | True |
Attaches additional metadata to a store's resources. | |
Tags | String | False |
Tags are additional short descriptors formatted as a string of comma-separated values. | |
TemplateSuffix | String | False |
States the name of the template a blog is using if it is using an alternate template. If a blog is using the default blog.liquid template, the value returned is null. | |
Title | String | False |
The title of the blog. | |
UpdatedAt | Datetime | True |
The date and time when changes were last made to the blog's properties. |
Query and delete information regarding different collects.
The Cloud processes all filters client-side within the Cloud. The following queries are the only ones processed server side:
SELECT * FROM CollectionListings
SELECT * FROM CollectionListings WHERE Id='123'
You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.
You must specify the Id of the collection listing to delete it.
DELETE FROM CollectionListings WHERE id ='123'
Name | Type | ReadOnly | References | Description |
Id [KEY] | Long | False |
The Id of the collection this listing belongs to. | |
Title | String | True |
The titke of the collection listing. | |
BodyHtml | String | True |
The HTML body/description for this listing. | |
DefaultProductImage | String | True |
The default product image in this listing. | |
ImageCreatedAt | String | True |
The date and time the image for this listing was created at. | |
ImageSource | String | True |
The source/link of the image for this listing. | |
Handle | String | True |
The handle of this listing. | |
UpdatedAt | Datetime | True |
The date and time when the collection listing was last modified. | |
PublishedAt | Datetime | True |
The date and time when the collection listing was created. |
Query, insert, or delete information regarding different collects.
The Cloud processes all filters client-side within the Cloud. The following queries are the only ones processed server side:
SELECT * FROM Collects
SELECT * FROM Collects WHERE Id='123'
You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.
You must specify the ProductId and CollectionId to create a collect.
INSERT INTO Collects (ProductId, CollectionId) VALUES ('512433520663','22892937239')
You must specify the Id of the collect to delete it.
DELETE FROM Collects WHERE id ='123'
Name | Type | ReadOnly | References | Description |
Id [KEY] | Long | True |
A unique numeric identifier for the collect. | |
CollectionId | Long | False |
The Id of the collection this collection is linked with. | |
ProductId | Long | False |
Products.Id |
The Id of the product this collection is linked with. |
Position | Int | True |
The position of this product in a manually sorted custom collection. The first position is 1. This value is applied only when the custom collection is sorted manually. | |
SortValue | String | True |
This is the same value as position but padded with leading zeroes to make it alphanumeric-sortable. | |
CreatedAt | Datetime | True |
The date and time when the collect was created. | |
UpdatedAt | Datetime | True |
The date and time when the collect was last modified. |
Create, read, update and delete the comments.
The Cloud processes all filters client-side within the Cloud. The following queries are the only ones processed server side:
SELECT * FROM Comments
SELECT * FROM Comments WHERE Id='123'
You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.
You must specify Article Id, Author, Body, Email to create a Comment.
INSERT INTO Comments(articleid,author,body,email) VALUES ('5557001','Harry','test','test@test.com')
You must specify the Id to update a Comment. For example:
UPDATE Comments SET Author='Adam' WHERE Id = '77171130'
You must specify the Id of the Comments to delete it.
DELETE FROM Comments WHERE Id = '555695'
Name | Type | ReadOnly | References | Description |
Id [KEY] | Long | True |
A unique numeric identifier for the comment. | |
BlogId | Long | False |
Blogs.Id |
A unique numeric identifier for the blog containing the article that the comment belongs to. |
ArticleId | Long | False |
Articles.Id |
A unique numeric identifier for the article that the comment belongs to. |
Author | String | False |
The name of the author of the comment. | |
Body | String | False |
The basic Textile markup of a comment. | |
BodyHtml | String | False |
The text of the comment, complete with HTML markup. | |
CreatedAt | Datetime | True |
The date and time (ISO 8601 format) when the comment was created. | |
String | False |
The email address of the author of the comment. | ||
Ip | String | False |
The IP address from which the comment was posted. | |
PublishedAt | String | False |
The date and time (ISO 8601 format) when the comment was published. | |
Status | String | True |
The status of the comment. The allowed values are pending, approved, unapproved, spam, published, removed. | |
UpdatedAt | Datetime | True |
The date and time (ISO 8601 format) when the comment was last modified. | |
UserAgent | String | False |
The user agent string provided by the software used to create the comment. |
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
Name | Type | Description |
published_status | String |
Filter comments by their publish status. Valid values are: published, unpublished and any ; default: any. |
status | String |
Filter comments by their status. Valid values are: published, pending and unapproved |
Create, select, update, and delete information regarding countries.
The Cloud processes all filters client-side within the Cloud. The following queries are the only ones processed server side:
SELECT * FROM Countries
SELECT * FROM Countries WHERE Id='123'
You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.
You must specify the Code to create a country.
INSERT INTO Countries (Code) VALUES ("FR")
You must specify the country Id to update a country. For example:
UPDATE Countries SET Tax=0.1 WHERE Id='123'
You must specify the Id of the country to delete it.
DELETE FROM Countries WHERE id ='123'
Name | Type | ReadOnly | References | Description |
Id [KEY] | Long | True |
A unique numeric identifier for the country. | |
Name | String | True |
The name of the country. | |
Code | String | False |
The code of the country. | |
Tax | Decimal | False |
The amount of tax which should be applied for this country. | |
Provinces | String | False |
The sub-regions of a country, such as its provinces or states. Each sub-region has the following properties: code: The two letter province or state code, country_id: The ID for the country to which this sub-region belongs, id: The ID for the particular sub-region, name: The name of the sub-region, tax: The tax value in decimal format, tax_name: The name of the tax as it is referred to in the applicable sub-region. For example, in Canada, the sales tax in the province Ontario is referred to as HST, tax_type: The tax type. Valid values: null, normal, or harmonized. If the value is harmonized, then the tax is compounded of the provincial and federal sales taxes, tax_percentage: The tax value in percent format |
Query, insert, update, or delete information regarding different custom collections.
The Cloud uses the Shopify API to process search criteria that refer to the Id, ProductId, Title, Handle, PublishedStatus, UpdatedAt, and PublishedAt columns. The supported SQL operators are '=' and 'IN' for Id, '=' for the ProductId, Title, Handle, and PublishedStatus columns, and '>' and '<' for UpdatedAt and PublishedAt. The Cloud processes other filters client-side within the Cloud. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.
For example, the following queries are processed server side.
SELECT * FROM CustomCollections
SELECT * FROM CustomCollections WHERE Id='123'
SELECT * FROM CustomCollections WHERE Id IN ('123','456')
SELECT * FROM CustomCollections WHERE ProductId='123'
SELECT * FROM CustomCollections WHERE Title='Ducks'
SELECT * FROM CustomCollections WHERE Handle='frontpage'
SELECT * FROM CustomCollections WHERE PublishedStatus='published'
SELECT * FROM CustomCollections WHERE UpdatedAt > '2018-05-02'
SELECT * FROM CustomCollections WHERE PublishedAt < '2017-08-15'
You must specify the Title to create a custom collection.
INSERT INTO CustomCollections (Title) VALUES ('Macbooks')
INSERT INTO CustomCollections (Title, Published) VALUES ('Macbooks', false)
You must specify the custom collection Id to update a custom collection. For example:
UPDATE CustomCollections SET Title='Updated title' WHERE Id='123'
You must specify the Id of the custom collection to delete it.
DELETE FROM CustomCollections WHERE Id ='123'
Name | Type | ReadOnly | References | Description |
Id [KEY] | Long | True |
A unique numeric identifier for the custom collection. | |
Title | String | False |
The title of the custom collection. | |
BodyHtml | String | False |
The body/description of the custom collection. | |
Image | String | False |
A JSON aggregate with information regarding the image of custom collection. | |
Metafields | String | False |
Attaches additional metadata to a shop's resources: key: An identifier for the metafield. (maximum: 30 characters), namespace: A container for a set of metadata. Namespaces help distinguish between metadata created by different apps. (maximum: 20 characters), value: Information to be stored as metadata, value_type: The information type being stored. Valid values: string or integer, description (optional): Additional information about the metafield . | |
Handle | String | False |
A human-friendly unique string for the custom collection automatically generated from its title. | |
SortOrder | String | False |
The order in which products in the custom collection appear. | |
TemplateSuffix | String | False |
The suffix of the liquid template being used. | |
PublishedScope | String | False |
The sales channels in which the custom collection is visible. | |
UpdatedAt | Datetime | True |
The date and time when the custom collection was last modified. | |
PublishedAt | Datetime | True |
The date and time when the custom collection was published to the Online Store channel. |
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
Name | Type | Description |
ProductId | String |
Show custom collections that include a given product. |
PublishedStatus | String |
Show custom collection with a given published status: published: show only published custom collections, unpublished: show only unpublished custom collections, any: show custom collections of any published status. (default: any). |
Create, update, delete, and query customer addresses.
The Cloud uses the Shopify API to process WHERE clause conditions built with the following column and operator. The Cloud processes other filters client-side within the Cloud.
For example, the following queries are processed server side:
SELECT * FROM CustomerAddresses
SELECT * FROM CustomerAddresses WHERE CustomerId='123'
SELECT * FROM CustomerAddresses WHERE CustomerId IN ('123','456')
SELECT * FROM CustomerAddresses WHERE CustomerUpdatedAt = '2018-05-04'
SELECT * FROM CustomerAddresses WHERE FirstName='abc'
SELECT * FROM CustomerAddresses WHERE LastName='xyz'
SELECT * FROM CustomerAddresses WHERE Company='cdata'
SELECT * FROM CustomerAddresses WHERE Address1='Electronic city'
SELECT * FROM CustomerAddresses WHERE City='Bangalore'
SELECT * FROM CustomerAddresses WHERE Country='India'
SELECT * FROM CustomerAddresses WHERE Phone='9769879890'
You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.
You must specify the customer Id to create an address.
INSERT INTO CustomerAddresses(CustomerId, Address1, City, Company) VALUES('123', '1 Rue des Carrieres', 'Suite 1234', 'Montreal')
You must specify the customer Id and the Id of the customer address to update an address.
UPDATE CustomerAddresses SET Zip='90210' WHERE CustomerId='123' AND Id='456'
You must specify the customer Id and the Id of the customer address to delete an address. You cannot delete a customer's default address.
DELETE FROM CustomerAddresses WHERE CustomerId='183467180055' AND Id='292265787415'
Name | Type | ReadOnly | References | Description |
Id [KEY] | Long | True |
A unique numeric identifier for the address. | |
CustomerId [KEY] | Long | False |
Customers.Id |
A unique numeric identifier for the customer. |
CustomerUpdatedAt | Datetime | True |
The date and time when the customer information was last updated. | |
Name | String | False |
The customer's name. | |
FirstName | String | False |
The customer's first name. | |
LastName | String | False |
The customer's last name. | |
Company | String | False |
The customer's company. | |
Address1 | String | False |
The customer's mailing address. | |
Address2 | String | False |
An additional field for the customer's mailing address. | |
City | String | False |
The customer's city. | |
Province | String | False |
The customer's province or state name. | |
Country | String | False |
The customer's country. | |
Zip | String | False |
The customer's zip or postal code. | |
Phone | String | False |
The customer's phone number for this mailing address. | |
ProvinceCode | String | False |
The two-letter pcode for the customer's province or state. | |
CountryCode | String | False |
The two-letter country code corresponding to the customer's country. | |
CountryName | String | False |
The customer's normalized country name. | |
Default | Boolean | True |
Indicates whether this address is the default address for the customer. |
Create, update, delete, and query customers.
The Cloud uses the Shopify API to process WHERE clause conditions built with the following column and operator. The Cloud processes other filters client-side within the Cloud.
SELECT * FROM Customers
SELECT * FROM Customers WHERE Id = '123'
SELECT * FROM Customers WHERE Id IN ('123', '456')
SELECT * FROM Customers WHERE CreatedAt >= '2017-10-25'
SELECT * FROM Customers WHERE CreatedAt <= '2017-10-25'
SELECT * FROM Customers WHERE UpdatedAt <= '2017-10-25'
SELECT * FROM Customers WHERE UpdatedAt >= '2017-10-25'
SELECT * FROM Customers WHERE FirstName = 'abc'
SELECT * FROM Customers WHERE LastName = 'xyz'
SELECT * FROM Customers WHERE Email = 'abcxyz@gmail.com'
SELECT * FROM Customers WHERE OrdersCount = 5
SELECT * FROM Customers WHERE Phone = '999999999'
SELECT * FROM Customers WHERE VerifiedEmail = true
SELECT * FROM Customers WHERE UpdatedAt = '2017-10-25'
You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.
The Email field is required to insert.
INSERT INTO Customers(Email, Phone) VALUES('steve.lastnameson@example.com', '+15142546011')
INSERT INTO Customers(Email, Phone, SendEmailInvite) VALUES('steve.lastnameson@example.com', '+15142546011', true)
You must specify the Id of the customer to update a customer.
UPDATE Customers SET Note='Customer is a great guy' WHERE Id='123'
UPDATE Customers SET Tags='New Customer, Repeat Customer' WHERE Id='123'
You must specify the Id of the customer to delete a customer.
DELETE FROM Customers WHERE Id='123'
Name | Type | ReadOnly | References | Description |
Id [KEY] | Long | True |
A unique numeric identifier for the customer. | |
LastOrderId | Long | True |
Orders.Id |
The id of the customer's last order. |
LastOrderName | String | True |
The name of the customer's last order. | |
FirstName | String | False |
The customer's first name. | |
LastName | String | False |
The customer's last name. | |
String | False |
The unique email address of the customer. | ||
Phone | String | False |
The unique phone number for this customer. | |
TaxExempt | Boolean | False |
Indicates whether the customer should be charged taxes when placing orders. | |
TotalSpent | Decimal | True |
The total amount of money that the customer has spent at the shop. | |
OrdersCount | Integer | True |
The number of orders associated with this customer. | |
AcceptsMarketing | Boolean | True |
Indicates whether the customer has consented to be sent marketing material via email. | |
MultipassIdentifier | String | True |
The customer's identifier used with Multipass login. | |
Note | String | False |
A note about the customer. | |
State | String | True |
The state of the customer's account in a shop. | |
Tags | String | False |
The tags for this customer. Separate with comma for multiple tags. | |
VerifiedEmail | Boolean | True |
States whether or not the email address has been verified. | |
CreatedAt | Datetime | True |
The date and time when the customer was created. | |
UpdatedAt | Datetime | True |
The date and time when the customer information was updated. |
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
Name | Type | Description |
Password | String |
Password of the customer |
PasswordConfirmation | String |
Password confirmation of the customer. |
SendEmailWelcome | Boolean |
Whether to send a welcome email to the customer or not. |
SendEmailInvite | Boolean |
Whether to send an invite email to the customer or not. |
Create, select, update, and delete information regarding discount codes.
The Cloud uses the Shopify API to process search criteria that refer to the PriceRuleId and Id columns. The supported SQL operator is '='. The Cloud processes other filters client-side within the Cloud.
For example, the following queries are processed server side: SELECT * FROM DiscountCodes WHERE PriceRuleId = '123' SELECT * FROM DiscountCodes WHERE PriceRuleId = '123' AND Id = '456'
You must specify the PriceRuleId and Code to create a discount code.
INSERT INTO DiscountCodes (PriceRuleId,Code) VALUES ('290807676951','SUMMERSALE100OFF')
You must specify the PriceRuleId and Id to update a discount code. For example:
UPDATE DiscountCodes SET Code = 'WINTERSALE500OFF' WHERE PriceRuleId = '123' AND Id = '456'
You must specify the Id of the custom collection to delete it.
DELETE FROM DiscountCodes WHERE PriceRuleId = '123' AND Id = '456'
Name | Type | ReadOnly | References | Description |
Id [KEY] | Long | True |
A unique numeric identifier for the discount code. | |
PriceRuleId [KEY] | Long | False |
PriceRules.Id |
The Id for the price rule that this discount code belongs to. |
Code | String | False |
The case-insensitive discount code that customers use at checkout. | |
UsageCount | Int | True |
The number of times that the discount code has been redeemed. | |
CreatedAt | Datetime | True |
The date and time when the discount code was created. | |
UpdatedAt | Datetime | True |
The date and time when the discount code was last modified. |
Create, update, delete, and query draft orders.
The Cloud uses the Shopify API to process search criteria that refer to the Id, Status, and UpdatedAt columns. The supported SQL operators are "=" and "IN" for Id, "=" for Status, and ">" and "<" for UpdatedAt. The Cloud processes other filters client-side within the Cloud. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.
For example, the following queries are processed server side:
SELECT * FROM DraftOrders
SELECT * FROM DraftOrders WHERE Id='123'
SELECT * FROM DraftOrders WHERE Id IN ('123', '456')
SELECT * FROM DraftOrders WHERE Status='completed'
SELECT * FROM DraftOrders WHERE UpdatedAt > '2018-02-05'
Create a simple draft order with only a product variant Id using aggregates.
INSERT INTO DraftOrders(LineAggregate) VALUES('[{\"variant_id\":\"4236041945111\",\"quantity\":2},{\"variant_id\":\"4236069011479\",\"quantity\":3}]')
Create a simple draft order with only a product variant Id using temporary table.
The temporary table you are populating is dynamic and will be created at run time the first time you insert to it. Temporary tables are denoted by a # appearing in their name. When using a temporary table to insert, the temporary table must be named in the format [TableName]#TEMP, where TableName is the name of the table you will be inserting to. For instance:
INSERT INTO OrdersItems#Temp (ItemVariantId,ItemQuantity) VALUES ('123',2)
Once your temporary table is populated, it is now time to insert to the actual table in Shopify. You can do this by performing an INSERT to the actual table and setting as a value for LinesAggregate the name of the temporary table. For instance:
INSERT INTO DraftOrders (LineAggregate) VALUES ('OrdersItems#Temp')
Create a custom draft order using aggregates.
INSERT INTO DraftOrders(LineAggregate, CustomerId, UseCustomerDefaultAddress) VALUES('[{\"title\":\"Custom Tee\",\"price\":20.15,\"quantity\":5}]', '709015339031', true)
Create a custom draft order using temporary table.
INSERT INTO OrdersItems#Temp (ItemTitle, ItemPrice, ItemQuantity) VALUES ('Custom Tee', 20.15, 5)
INSERT INTO DraftOrders(LineAggregate, CustomerId, UseCustomerDefaultAddress) VALUES('OrdersItems#Temp', '709015339031', true)
Add a Note to a draft order:
UPDATE DraftOrders SET Note = 'Customer contacted us about a custom engraving on this iPod' WHERE Id = '123'
Set a discount on a draft order:
UPDATE DraftOrders SET AppliedDiscountDescription = 'Custom discount', AppliedDiscountValueType = 'percentage', AppliedDiscountValue = 10.0, AppliedDiscountAmount = 19.90, AppliedDiscountTitle = 'Custom' WHERE Id = '123'
You must specify the Id of the draft order to delete it.
DELETE FROM DraftOrders WHERE Id = '123'
Name | Type | ReadOnly | References | Description |
Id [KEY] | Long | True |
A unique numeric identifier for the order. | |
CustomerId | Long | False |
Customers.Id |
A unique numeric identifier for the customer. |
Currency | String | False |
The three letter code (ISO 4217) for the currency used for the payment. | |
String | False |
The customer's email address. | ||
Name | String | True |
The customer's order name as represented by a number. | |
TotalPrice | Decimal | True |
The sum of all the prices of all the items in the order, taxes and discounts included. | |
SubtotalPrice | Decimal | True |
Price of the order before shipping and taxes. | |
TotalTax | Decimal | False |
The sum of all the taxes applied to the order. | |
TaxesIncluded | Boolean | False |
States whether or not taxes are included in the order subtotal. | |
InvoiceSentAt | Datetime | True |
This auto-generated property is the date and time when the invoice was emailed to the customer. Only available for draft orders. | |
Note | String | False |
The text of an optional note that a shop owner can attach to the order. | |
Tags | String | False |
Additional short descriptors. | |
InvoiceUrl | String | True |
The URL for the invoice. Only available for draft orders. | |
TaxExempt | Boolean | True |
States whether or not taxes are exempt for this order. Only available for draft orders. | |
Status | String | True |
The status of the order. Valid values: open: all open orders (default), invoice_sent: show only closed orders, completed: Show only cancelled orders. | |
LineAggregate | String | False |
A JSON aggregate of line items associated with the draft order. | |
BillingAddressFirstName | String | False |
The first name of the person associated with the payment method. | |
BillingAddressLastName | String | False |
The last name of the person associated with the payment method. | |
BillingAddressAddress1 | String | False |
The street address of the billing address. | |
BillingAddressAddress2 | String | False |
An optional additional field for the street address of the billing address. | |
BillingAddressPhone | String | False |
The phone number at the billing address. | |
BillingAddressCity | String | False |
The city of the billing address. | |
BillingAddressCompany | String | False |
The company of the person associated with the billing address. | |
BillingAddressZip | String | False |
The zip or postal code of the billing address. | |
BillingAddressProvince | String | False |
The name of the state or province of the billing address. | |
BillingAddressCountry | String | False |
The name of the country of the billing address. | |
BillingAddressLatitude | Double | False |
The latitude of the billing address. | |
BillingAddressLongitude | Double | False |
The longitude of the billing address. | |
BillingAddressName | String | False |
The full name of the person associated with the payment method. | |
BillingAddressCountryCode | String | False |
The two-letter code for the country of the billing address. | |
BillingAddressProvinceCode | String | False |
The two-letter abbreviation of the state or province of the billing address. | |
BillingAddressDefault | Boolean | False |
Whether this address is the default one or not. | |
ShippingAddressFirstName | String | False |
The first name of the person associated with the shipping method. | |
ShippingAddressLastName | String | False |
The last name of the person associated with the shipping method. | |
ShippingAddressAddress1 | String | False |
The street address of the shipping address. | |
ShippingAddressAddress2 | String | False |
An optional additional field for the street address of the shipping address. | |
ShippingAddressPhone | String | False |
The phone number at the shipping address. | |
ShippingAddressCity | String | False |
The city of the shipping address. | |
ShippingAddressCompany | String | False |
The company of the person associated with the shipping address. | |
ShippingAddressZip | String | False |
The zip or postal code of the shipping address. | |
ShippingAddressProvince | String | False |
The name of the state or province of the shipping address. | |
ShippingAddressCountry | String | False |
The name of the country of the shipping address. | |
ShippingAddressLatitude | Double | False |
The latitude of the shipping address. | |
ShippingAddressLongitude | Double | False |
The longitude of the shipping address. | |
ShippingAddressName | String | False |
The full name of the person associated with the shipping method. | |
ShippingAddressCountryCode | String | False |
The two-letter code for the country of the shipping address. | |
ShippingAddressProvinceCode | String | False |
The two-letter abbreviation of the state or province of the shipping address. | |
ShippingAddressDefault | Boolean | False |
Whether this address is the default one or not. | |
AppliedDiscountTitle | String | False |
The title of the applied discount for this order. Only available for draft orders. | |
AppliedDiscountDescription | String | False |
The description of the applied discount for order. Only available for draft orders. | |
AppliedDiscountValue | Decimal | False |
The value of the applied discount for this order. Only available for draft orders. | |
AppliedDiscountValueType | String | False |
The value type of the applied discount for order. Only available for draft orders. | |
AppliedDiscountAmount | Decimal | False |
The amount of the applied discount for this order. Only available for draft orders. | |
CreatedAt | Datetime | True |
The date and time when the order was created. | |
UpdatedAt | Datetime | True |
The date and time when the order was last modified. | |
CompletedAt | Datetime | True |
The date and time when the order was completed at. |
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
Name | Type | Description |
UseCustomerDefaultAddress | Boolean |
Optional boolean that you can send as part of a draft order object to load customer shipping information. Valid values: true or false. |
Create, delete, and query information regarding fulfillment events.
The OrderId and FulfillmentId are required to query this table.
The Cloud uses the Shopify API to process search criteria that refer to the OrderId, FulfillmentId, and Id columns. The supported SQL operator is '='. The Cloud processes other filters client-side within the Cloud. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.
For example, the following queries are processed server side:
SELECT * FROM FulfillmentEvents WHERE OrderId='123' AND FulfillmentId='456'
SELECT * FROM FulfillmentEvents WHERE OrderId='123' AND FulfillmentId='456' AND Id='789'
You must specify the OrderId, FulfillmentId, and Status to create a fulfillment event.
INSERT INTO FulfillmentEvents (Orderid,FulfillmentId, Status,Message,EstimatedDeliveryAt) VALUES ('202520330263','206541914135', 'in_transit','test','2018-08-02 10:15:25 PM')
You must specify the OrderId, FulfillmentId, and Id of the fulfillment event to delete it.
DELETE FROM FulfillmentEvents WHERE Orderid='202520330263' AND FulfillmentId='206541914135' AND Id='2374424264727'
Name | Type | ReadOnly | References | Description |
Id [KEY] | Long | True |
A numeric unique identifier for the fulfillment event. | |
OrderId [KEY] | Long | False |
Orders.Id |
The id of the order the fulfillment event belongs to. |
FulfillmentId [KEY] | Long | False |
Fulfillments.Id |
A numeric unique identifier for the fulfillment to which the fulfillment event belongs. |
ShopId | Long | True |
Shop.Id |
A numeric unique identifier for the shop to which the fulfillment event belongs. |
Status | String | False |
The status of the fulfillment event. Valid values are: confirmed, in_transit, out_for_delivery, delivered, failure. | |
EstimatedDeliveryAt | Datetime | False |
The status of the fulfillment event. Valid values are: confirmed, in_transit, out_for_delivery, delivered, failure. | |
Message | String | False |
An arbitrary message describing the status. Can be provided by a shipping carrier. | |
City | String | False |
The city in which the fulfillment event occurred. | |
Province | String | False |
The province in which the fulfillment event occurred. | |
Zip | String | False |
The zip code in the location in which the fulfillment event occurred. | |
Country | String | False |
The country in which the fulfillment event occurred. | |
Address1 | String | False |
The fulfillment event's street address. | |
Latitude | Double | False |
Geographic coordinate specifying the north/south location of a fulfillment event. | |
Longitude | Double | False |
Geographic coordinate specifying the east/west location of a fulfillment event. | |
CreatedAt | Datetime | True |
The date and time when the fulfillment event was created. | |
UpdatedAt | Datetime | True |
The date and time when the fulfillment event was updated. | |
HappenedAt | Datetime | True |
The date and time when the fulfillment event occurred. |
Create, update, and query fulfillments.
The Cloud uses the Shopify API to process search criteria that refer to the Id, OrderId, CreatedAt, and UpdatedAt columns. The Cloud processes other filters client-side within the Cloud.
For example, the following queries are processed server side.
If you specify the unique identifier of the order, then this view will only list fulfillment information concerning that order. You can also retrieve a specific fulfillment by specifying OrderId and Id.
SELECT * FROM Fulfillments
SELECT * FROM Fulfillments WHERE OrderId = '123'
SELECT * FROM Fulfillments WHERE OrderId = '123' AND Id='567'
This view supports filtering by the CreatedAt and UpdatedAt columns on the server side.
SELECT * FROM Fulfillments WHERE CreatedAt > '2017-10-25'
You must specify the OrderId column to insert a fulfillment.
Fulfill one line item using aggregates.
INSERT INTO Fulfillments(OrderId, LineAggregate) VALUES('123', '[{\"id\":\"123\"}]')
Fulfill one line item using temporary table.
The temporary table you are populating is dynamic and will be created at run time the first time you insert to it. Temporary tables are denoted by a # appearing in their name. When using a temporary table to insert, the temporary table must be named in the format [TableName]#TEMP, where TableName is the name of the table you will be inserting to. For instance:
Insert INTO OrdersItems#Temp (ItemId) Values ('123')
Once your temporary table is populated, it is now time to insert to the actual table in Shopify. You can do this by performing an INSERT to the actual table and setting as a value for LinesAggregate the name of the temporary table. For instance:
Insert INTO Fulfillments (OrderId, LinesAggregate) VALUES ('123', 'OrdersItems#Temp')
In cases that two ore more shop locations are specified in your Shopify account the LocationId needs to be added. For instance:
Insert INTO Fulfillments (OrderId, LocationId, LinesAggregate) VALUES ('123', '456', 'OrdersItems#Temp')
Fulfill many line items using aggregates.
INSERT INTO Fulfillments(OrderId, LineAggregate) VALUES('123', '[{\"id\":\"123\"},{\"id\":\"456\"},{\"id\":\"789\"}]')
Fulfill many line items using temporary table.
INSERT INTO OrdersItems#Temp (ItemId) VALUES ('123')
INSERT INTO OrdersItems#Temp (ItemId) VALUES ('456')
INSERT INTO OrdersItems#Temp (ItemId) VALUES ('789')
INSERT INTO Fulfillments (OrderId, LineAggregate) VALUES ('123', 'OrdersItems#Temp')
Fulfill all line items, notify the customer, and set a tracking number.
INSERT INTO Fulfillments(OrderId, TrackingNumbers, NotifyCustomer) VALUES('123', 'FEDEX1', true)
Partially fulfill a single line item by explicitly specifying the line item and quantity to be fulfilled using aggregates.
INSERT INTO Fulfillments(OrderId, LineAggregate) VALUES('123', '[{\"id\":\"123\",\"quantity\":5}]')
Partially fulfill a single line item by explicitly specifying the line item and quantity to be fulfilled using temporary table.
INSERT INTO OrdersItems#Temp (ItemId,ItemQuantity) VALUES ('123',5)
INSERT INTO Fulfillments (OrderId,LineAggregate) VALUES ('456','OrdersItems#Temp')
Complete a fulfillment (you must specify the Id of the fulfillment as well).
INSERT INTO Fulfillments(OrderId, Id, Operation) VALUES('123', '456', 'Complete')
Transition a fulfillment from pending to open (you must specify the Id of the fulfillment as well).
INSERT INTO Fulfillments(OrderId, Id, Operation) VALUES('123', '456', 'Open')
Cancel a fulfillment (you must specify Id of the fulfillment as well).
INSERT INTO Fulfillments(OrderId, Id, Operation) VALUES('123', '456', 'Cancel')
You must specify the OrderId and Id of the fulfillment to fulfill an order.
UPDATE Fulfillments SET TrackingNumbers='FedEx123,UPS123' WHERE OrderId='123' AND Id='456'
Name | Type | ReadOnly | References | Description |
Id [KEY] | Long | True |
A unique numeric identifier for the fulfillment. | |
OrderId [KEY] | Long | False |
Orders.Id |
A unique numeric identifier for the order. |
LocationId [KEY] | Long | False |
A unique numeric identifier for the Location. | |
NotifyCustomer | Boolean | False |
A flag indicating whether the customer should be notified | |
Status | String | True |
The status of the fulfillment. | |
Receipt | String | True |
Provides information about the receipt of this fulfillment. | |
TrackingCompany | String | False |
The name of the tracking company. | |
TrackingNumbers | String | False |
A list of comma-separated tracking numbers, provided by the shipping company. | |
TrackingUrls | String | True |
The sum of all the prices of all the items in the fulfillment. | |
VariantInventoryManagement | String | True |
States the name of the inventory management service. | |
CreatedAt | Datetime | True |
The date and time when the fulfillment was created. | |
UpdatedAt | Datetime | True |
The date and time when the fulfillment was last modified. | |
LineAggregate | String | False |
A JSON aggregate of line items associated with the fulfillment. | |
OrderUpdatedAt | Datetime | True |
The date and time when the order was last modified. | |
OrderCreatedAt | Datetime | True |
The date and time when the order was last created. | |
Operation | String | False |
An operation to apply to the fulfillment. Complete, Open, or Cancel. The allowed values are Complete, Open, Cancel. |
Query, create, update, and delete information regarding different fulfillment services.
The Cloud uses the Shopify API to process search criteria that refer to the Id column. The supported SQL operator is '='. The Cloud processes other filters client-side within the Cloud. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.
For example, the following queries are processed server side:
SELECT * FROM FulfillmentServices
SELECT * FROM FulfillmentServices WHERE Id='123'
You must specify the Name, CallbackUrl, and Format to create a fulfillment service.
INSERT INTO FulfillmentServices (Name,CallbackUrl,Format) VALUES ('testing fulfillment services','http://google.com','json')
You must specify the Id to update a fulfillment service. For example:
UPDATE FulfillmentServices Set Name='test32', IncludePendingStock=true WHERE Id='123'
You must specify the Id of the fulfillment service to delete it.
DELETE FROM FulfillmentServices WHERE Id='123'
Name | Type | ReadOnly | References | Description |
Id [KEY] | Long | True |
A unique numeric identifier for the fulfillment service. | |
LocationId | Long | True |
Locations.Id |
The unique identifier of the location tied to the fulfillment service. |
ProviderId | String | True |
A unique identifier for the fulfillment service provider. | |
Name | String | False |
The name of the fulfillment service as seen by merchants and their customers. | |
Handle | String | True |
A human-friendly unique string for the fulfillment service generated from its title. | |
ServiceName | String | True |
The name of the fulfillment service. | |
String | False |
The email of the fulfillment service. | ||
IncludePendingStock | Boolean | False |
States if the fulfillment service includes a pending stock. | |
RequiresShippingMethod | Boolean | False |
States if the fulfillment service requires products to be physically shipped. Valid values are 'true' and 'false'. | |
TrackingSupport | Boolean | False |
States if the fulfillment service provides tracking numbers for packages. Valid values are | |
InventoryManagement | Boolean | False |
States if the fulfillment service tracks product inventory and provides updates to Shopify. Valid values are | |
FulfillmentOrdersOptIn | Boolean | True |
States if the fulfillment orders is opt in. | |
CallbackUrl | String | False |
States the URL endpoint that Shopify needs to retrieve inventory and tracking updates. This field is necessary if either inventory_management or tracking_support is set to | |
Format | String | False |
Specifies the format of the API output. Valid values are json and xml. |
Query and update information regarding different inventory items.
The Cloud uses the Shopify API to process search criteria that refer to the Id column. The supported SQL operators are '=' and 'IN'. The Cloud processes other filters client-side within the Cloud.
For example, the following queries are processed server side:
SELECT * FROM InventoryItems
SELECT * FROM InventoryItems WHERE Id='123'
SELECT * FROM InventoryItems WHERE Id IN ('123','456')
You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false; the Shopify APIs require Id in the search criteria, and any other search criteria will cause an error.
You must specify the Id to update an inventory item. For example:
UPDATE InventoryItems Set Sku='lamo', Tracked=false WHERE Id='123'
Name | Type | ReadOnly | References | Description |
Id [KEY] | Long | True |
A unique numeric identifier for the fulfillment. | |
Cost | Decimal | False |
The unit cost of the inventory item. | |
CountryCodeOfOrigin | String | False |
The two-digit code for the country where the inventory item was made. | |
CountryHarmonizedSystemCodes | String | False |
An array of country-specific Harmonized System (HS) codes for the item. | |
HarmonizedSystemCode | String | False |
The general Harmonized System (HS) code for the inventory item. Used if a country-specific HS code is not available. | |
ProvinceCodeOfOrigin | String | False |
The two-digit code for the province where the inventory item was made. Used only if the shipping provider for the inventory item is Canada Post. | |
SKU | String | False |
The unique SKU (stock keeping unit) of the inventory item. | |
Tracked | Boolean | False |
Whether the inventory item is tracked. If true, then inventory quantity changes are tracked by Shopify. | |
RequiresShipping | Boolean | True |
Whether a customer needs to provide a shipping address when placing an order containing the inventory item. | |
CreatedAt | Datetime | True |
The date and time when the inventory item was created. | |
UpdatedAt | Datetime | True |
The date and time when the inventory item was last updated. |
Query, create, and update information regarding different inventory levels.
To query this table InventoryItemId, LocationId or both should be specified. The supported SQL operators are '=' and 'IN'. The Cloud processes other filters client-side within the Cloud. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.
For example, the following queries are processed server side:
SELECT * FROM InventoryLevels WHERE InventoryItemId='123'
SELECT * FROM InventoryLevels WHERE InventoryItemId='123' AND LocationId='456'
SELECT * FROM InventoryLevels WHERE InventoryItemId IN ('123','456) AND LocationId IN ('123','456')
Adjust the available quantity of an inventory item at a single location:
INSERT INTO InventoryLevels (InventoryItemId, LocationId, Available, Operation) VALUES (534169354263,1448280087,5,'Adjust')
Connects an inventory item to a location by creating an inventory level at that location:
INSERT INTO InventoryLevels (InventoryItemId, LocationId, Operation) VALUES (534169354263,4484497431,'Connect')
Sets the inventory level for an inventory item at a location:
INSERT INTO InventoryLevels (InventoryItemId, LocationId, Available,Operation, DisconnectIfNecessary) VALUES (534169354263,4484497431,42,'Set', true)
You must specify the InventoryItemId and LocationId to delete an inventory level of an inventory item at a location.
DELETE FROM InventoryLevels WHERE InventoryItemId='153681943' AND LocationId='45678'
Name | Type | ReadOnly | References | Description |
InventoryItemId [KEY] | Long | False |
The ID of the inventory item that the inventory level belongs to. | |
LocationId [KEY] | Long | False |
Locations.Id |
The ID of the location that the inventory level belongs to. To find the ID of the location, use the Locations view. |
Available | Int | False |
The quantity of inventory items available for sale. Returns null if the inventory item is not tracked. | |
UpdatedAt | Datetime | True |
The date and time (ISO 8601 format) when the inventory level was last modified. | |
Operation | String | False |
An operation to apply to the InventoryLevel. Valid values for order: Adjust, Connect, Set. The allowed values are Adjust, Connect, Set. |
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
Name | Type | Description |
RelocateIfNecessary | Boolean |
Whether inventory for any previously connected locations will be relocated. This property is ignored when no fulfillment service location is involved. Used in insertions. |
DisconnectIfNecessary | Boolean |
Whether inventory for any previously connected locations will be set to 0 and the locations disconnected. This property is ignored when no fulfillment service is involved. Used in insertions. |
Create, update, delete, and query marketing events.
The Cloud processes all filters client-side within the Cloud. The following queries are the only ones processed server side:
SELECT * FROM MarketingEvents
SELECT * FROM MarketingEvents WHERE Id='123'
You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.
You must specify the MarketingChannel, EventType, Paid, ReferringDomain, and StartedAt columns to create a marketing event. For example:
INSERT INTO MarketingEvents (MarketingChannel,EventType,Paid,ReferringDomain,StartedAt) VALUES ('social', 'ad',true,'facebook.com','2018-12-15')
You must specify the Id to update a marketing event. You can modify only timestamps, RemoteId, and budget/currency. For example:
UPDATE MarketingEvents SET RemoteId='1000:2000',StartedAt='2018-02-02T00:00 +00:00',EndedAt='2018-02-03T00:00 +00:00',ScheduledToEndAt='2018-02-04T00:00 +00:00',Budget=11.1, BudgetType='daily', Currency='USD' WHERE Id='123'
You must specify the Id of the marketing event to delete it.
DELETE FROM MarketingEvents WHERE Id ='123'
Name | Type | ReadOnly | References | Description |
Id [KEY] | Long | True |
A unique numeric identifier for the marketing event. | |
RemoteId | String | False |
An optional remote identifier for a marketing event. | |
BreadcrumbId | String | True |
An optional identifier for the breadcrumb of a marketing event. | |
UTMCampaign | String | True |
A unique name for the UTM campaign. | |
UTMSource | String | True |
The source of the UTM campaign. | |
UTMMedium | String | True |
The medium of the UTM campaign. | |
UTMContent | String | True |
The content of the UTM campaign. | |
UTMTerm | String | True |
The term of the UTM campaign. | |
EventTarget | String | True |
The target of the event. | |
Description | String | True |
A description for the marketing event. | |
MarketingChannel | String | False |
A broader marketing event type that is focused only on the channel. Must be one of the allowed values (`search`, `display`, `social`, `email`, `referral`). | |
EventType | String | False |
The specific type of marketing event. Must be one of the allowed values (`ad`, `post`, `message`, `retargeting`, `transactional`, `affiliate`, `loyalty`, `newsletter`, `abandoned_cart`, `receipt`). | |
Budget | Decimal | False |
The budget of the ad campaign. | |
Paid | Boolean | False |
A boolean field to specify whether this event is paid or organic. | |
BudgetType | String | False |
The type of the budget; must be either `daily` or `lifetime`. | |
Currency | String | False |
The currency for the budget. | |
ManageUrl | String | True |
A link to manage the marketing event, generally in the Shopify app's interface. | |
PreviewUrl | String | True |
A link to view the live version of the post/ad, or to view a rendered preview of the post/ad/email in the Shopify app. | |
ReferringDomain | String | False |
The destination domain of the marketing event. Required unless MarketingChannel is one of email/referral/display. | |
MarketedResources | String | True |
A list of the items that were marketed in the marketing event. It's a list of dictionaries with type keys and id keys. Valid values for type are: (`product`, `collection`, `price_rule`, `page`, `article`, `homepage`). All types, other than homepage, also require an id. | |
StartedAt | Datetime | False |
The timestamp when the marketing action was started, or when the email was sent, or when the Facebook post was made live, etc. | |
EndedAt | Datetime | False |
For events with a duration, when the event actually ended. This may differ from ScheduledToEndAt, if the ad was stopped early, etc. | |
ScheduledToEndAt | Datetime | False |
For events with a duration, when the event was supposed to end. |
Retrieves a list of metafields that belong to a resource.
The Cloud uses the Shopify API to process search criteria that refer to the Id, OwnerId and OwnerResource columns. The supported SQL operators are '=,IN' for the Id, OwnerResource and OwnerId columns. The Cloud processes other filters client-side within the Cloud. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.
For example, the following queries are processed server side:
SELECT * FROM Metafields WHERE Id = 721389482
SELECT * FROM Metafields WHERE ID IN (SELECT Id FROM Customers)
SELECT * FROM Metafields WHERE ID IN (19422939054103,19422939185175,19422939250711,19431415152663,19431415185431)
SELECT * FROM Metafields WHERE OwnerId = 64146833431 AND OwnerResource = 'product'
SELECT * FROM Metafields WHERE OwnerId = 64146833431 AND OwnerResource IN ('product', 'customer')
SELECT * FROM Metafields WHERE OwnerId IN (64146833430, 64146833431) AND OwnerResource = 'product'
SELECT * FROM Metafields WHERE OwnerResource = 'customer' AND OwnerId IN (SELECT Id FROM Customers);
You must specify the NameSpace, Key, and Value to create Metafields.
INSERT INTO Metafields (NameSpace,Key,Value,Type,Description,OwnerResource) VALUES ('inventory','warehouse',25,'number_integer','This is description about data','factory')
You must specify the Id to update Metafields. For example:
UPDATE Metafields SET Value = 26 WHERE Id = 19766614851607
You must specify the Id of the Metafields to delete it.
DELETE FROM Metafields WHERE Id = 19766614851607
Name | Type | ReadOnly | References | Description |
Id [KEY] | Long | True |
The unique ID of the metafield. | |
Namespace | String | False |
A container for a set of metafields. You need to define a custom namespace for your metafields to distinguish them from the metafields used by other apps. | |
Key | String | False |
The name of the metafield. | |
Value | String | False |
The information to be stored as metadata. | |
Type | String | False |
The metafield's information type. | |
Description | String | False |
A description of the information that the metafield contains. | |
OwnerId | Long | False |
The unique ID of the resource that the metafield is attached to. | |
OwnerResource | String | False |
The type of resource that the metafield is attached to. The allowed values are shop, draft_order, product, variant, page, article, order, customer, collection, blog, product_image. | |
CreatedAt | Datetime | True |
The date and time (ISO 8601 format) when the metafield was created. | |
UpdatedAt | Datetime | True |
The date and time (ISO 8601 format) when the metafield was last updated. | |
AdminGraphqlApiId | String | True |
The admin graphql api id |
Create, update, delete, and query order risks.
Name | Type | ReadOnly | References | Description |
Id [KEY] | Long | True |
A unique numeric identifier for the order risk. | |
CauseCancel | Boolean | False |
Whether this order risk is severe enough to force the cancellation of the order. | |
CheckoutId | Long | True |
The ID of the checkout that the order risk belongs to. | |
Display | Boolean | False |
Whether the order risk is displayed on the order details page in the Shopify admin. | |
Message | String | False |
The message that's displayed to the merchant to indicate the results of the fraud check. | |
OrderId | Long | True |
Orders.Id |
The ID of the order that the order risk belongs to. |
Recommendation | String | False |
The recommended action given to the merchant. The allowed values are cancel, investigate, accept. | |
Score | Decimal | False |
For internal use only. A number between 0 and 1 that's assigned to the order. The closer the score is to 1, the more likely it is that the order is fraudulent. | |
Source | String | False |
The source of the order risk. |
Create, update, delete, and query orders.
The Cloud uses the Shopify API to process search criteria that refer to the Id, FinancialStatus, FulfillmentStatus, CreatedAt, ProcessedAt and UpdatedAt columns. The supported SQL operators are '=' and 'IN' for Id, '=' for FinancialStatus, and FulfillmentStatus, and '>' and '<' for the CreatedAt, ProcessedAt, and UpdatedAt columns. The Cloud processes other filters client-side within the Cloud. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.
For example, the following queries are processed server side:
SELECT * FROM Orders
SELECT * FROM Orders WHERE Id='123'
SELECT * FROM Orders WHERE FinancialStatus = 'pending'
SELECT * FROM Orders WHERE CreatedAt > '2017-10-25'
Create a simple order with only a product variant Id using aggregates.
INSERT INTO Orders(LineAggregate) VALUES('[{\"variant_id\":\"4236041945111\",\"quantity\":2}]')
Create a simple order with only a product variant Id using temporary table.
The temporary table you are populating is dynamic and will be created at run time the first time you insert to it. Temporary tables are denoted by a # appearing in their name. When using a temporary table to insert, the temporary table must be named in the format [TableName]#TEMP, where TableName is the name of the table you will be inserting to. For instance:
INSERT INTO OrdersItems#Temp (ItemVariantId,ItemQuantity) VALUES ('123',2)
Once your temporary table is populated, it is now time to insert to the actual table in Shopify. You can do this by performing an INSERT to the actual table and setting as a value for LinesAggregate the name of the temporary table. For instance:
INSERT INTO Orders (LineAggregate) VALUES ('OrdersItems#Temp')
Create a simple order, sending the order receipt and fulfillment receipt to the customer using aggregates.
INSERT INTO Orders(Email, FulfillmentStatus, SendReceipt, SendFulfillmentReceipt, LineAggregate) VALUES('foo@example.com', 'fulfilled', true, true, '[{\"variant_id\":\"4236041945111\",\"quantity\":2},{\"variant_id\":\"4236069011479\",\"quantity\":3}]')
Create a simple order, sending the order receipt and fulfillment receipt to the customer using temporary table.
INSERT INTO OrdersItems#Temp (ItemVariantId,ItemQuantity) VALUES ('4236041945111',2)
INSERT INTO OrdersItems#Temp (ItemVariantId,ItemQuantity) VALUES ('4236069011479',3)
INSERT INTO Orders (Email, FulfillmentStatus, SendReceipt, SendFulfillmentReceipt,LineAggregate) VALUES ('foo@example.com', 'fulfilled', true, true,'OrdersItems#Temp')
Create a simple order, with taxes using aggregates.
INSERT INTO Orders(LineAggregate, TaxAggregate) VALUES('[{\"variant_id\":\"4236041945111\",\"quantity\":2},{\"variant_id\":\"4236069011479\",\"quantity\":3}]', '[{\"price\":10.2,\"rate\":0.06,\"title\":\"State Tax\"}]')
Create a simple order, with taxes using temporary table.
INSERT INTO OrdersItems#Temp (ItemVariantId,ItemQuantity) VALUES ('4236041945111',2)
INSERT INTO OrdersItems#Temp (ItemVariantId,ItemQuantity) VALUES ('4236069011479',3)
INSERT INTO TaxItems#Temp (TaxItemPrice, TaxItemRate,TaxItemTitle) VALUES (10.2, 0.06, 'State Tax')
INSERT INTO Orders (LineAggregate,TaxAggregate) VALUES ('OrdersItems#Temp','TaxItems#Temp')
Create a simple order, with multiple taxes using aggregates.
INSERT INTO Orders(LineAggregate, TaxAggregate) VALUES('[{\"variant_id\":\"4236041945111\",\"quantity\":2},{\"variant_id\":\"4236069011479\",\"quantity\":3}]', '[{\"price\":10.2,\"rate\":0.06,\"title\":\"State Tax\"},{\"price\":4.25,\"rate\":0.025,\"title\":\"County Tax\"}]')
Create a simple order, with multiple taxes using temporary table.
INSERT INTO OrdersItems#Temp (ItemVariantId,ItemQuantity) VALUES ('4236041945111',2)
INSERT INTO OrdersItems#Temp (ItemVariantId,ItemQuantity) VALUES ('4236069011479',3)
INSERT INTO TaxItems#Temp (TaxItemPrice, TaxItemRate,TaxItemTitle) VALUES (10.2, 0.06, 'State Tax')
INSERT INTO TaxItems#Temp (TaxItemPrice, TaxItemRate,TaxItemTitle) VALUES (4.25, 0.025, 'County Tax')
INSERT INTO Orders (LineAggregate,TaxAggregate) VALUES ('OrdersItems#Temp','TaxItems#Temp')
Create a simple partially paid order containing a captured transaction using aggregates.
INSERT INTO Orders(LineAggregate, FinancialStatus, TransactionAggregate) VALUES('[{\"title\":\"Big Brown Bear Boots\",\"price\":25.99,\"quantity\":2}]', 'partially_paid', '[{\"status\":\"success\",\"kind\":\"capture\",\"amount\":52.57,\"gateway\":\"bogus\"}]')
Create a simple partially paid order containing a captured transaction using temporary table.
INSERT INTO OrdersItems#Temp (ItemTitle,ItemQuantity,ItemPrice) VALUES ('Big Brown Bear Boots',2,25.99)
INSERT INTO Transactions#Temp (TransactionItemAmount, TransactionItemKind, TransactionItemStatus, TransactionItemGateway) VALUES (52.57, 'capture', 'success', 'bogus')
INSERT INTO Orders (LineAggregate,FinancialStatus,TransactionAggregate) VALUES ('OrdersItems#Temp','partially_paid','Transactions#Temp')
Create a more comprehensive order using aggregates.
INSERT INTO Orders(LineAggregate) VALUES([{\"title\":\"Big Brown Bear Boots\",\"price\":74.99,\"grams\":1300,\"quantity\":3}])
Create a more comprehensive order using temporary table.
INSERT INTO OrdersItems#Temp (ItemTitle,ItemPrice,ItemGrams,ItemQuantity) VALUES ('Big Brown Bear Boots', 74.99, 1300, 3)
INSERT INTO Orders (LineAggregate) VALUES ('OrdersItems#Temp')
Create a pending order with an existing customer using aggregates.
INSERT INTO Orders(LineAggregate, CustomerId, FinancialStatus) VALUES('[{\"variant_id\":\"123\",\"quantity\":3}]', '456', 'pending')
Create a pending order with an existing customer using temporary table.
INSERT INTO OrdersItems#Temp (ItemVariantId, ItemQuantity) VALUES ('123', 3)
INSERT INTO Orders (LineAggregate,CustomerId, FinancialStatus) VALUES ('OrdersItems#Temp','456', 'pending')
Create a partially paid order with a new customer and addresses using aggregates.
INSERT INTO Orders(LineAggregate, CustomerFirstName, CustomerLastName, CustomerEmail, FinancialStatus) VALUES('[{\"variant_id\":\"123\",\"quantity\":3}]', 'Paul', 'Norman', 'paul.norman@example.com', 'partially_paid')
Create a partially paid order with a new customer and addresses using temporary.
INSERT INTO OrdersItems#Temp (ItemVariantId, ItemQuantity) VALUES ('123', 3)
INSERT INTO Orders(LineAggregate, CustomerFirstName, CustomerLastName, CustomerEmail, FinancialStatus) VALUES('OrdersItems#Temp', 'Paul', 'Norman', 'paul.norman@example.com', 'partially_paid')
Close an order (you must specify the Id of the order).
INSERT INTO Orders(Id, Operation) VALUES('123', 'Close')
Reopen an order (you must specify the Id of the order).
INSERT INTO Orders(Id, Operation) VALUES('123', 'Open')
Cancel an order (you must specify the Id of the order).
INSERT INTO Orders(Id, Operation) VALUES('123', 'Cancel')
Only columns BuyerAcceptsMarketing, Email, Phone, Note, Tags, ShippingAddressFirstName, ShippingAddressLastName, ShippingAddressAddress1, ShippingAddressAddress2, ShippingAddressPhone, ShippingAddressCity, ShippingAddressCompany, ShippingAddressZip, ShippingAddressProvince, ShippingAddressCountry, ShippingAddressLatitude, ShippingAddressLongitude, ShippingAddressName, ShippingAddressCountryCode, ShippingAddressProvinceCode and ShippingAddressDefault can be updated.
Update the shipping address of an existing order.
UPDATE Orders SET ShippingAddressAddress1='123 Ship Street', ShippingAddressCity='Shipsville' WHERE Id='123'
Update an order's tags.
UPDATE Orders SET Tags='External, Inbound, Outbound' WHERE Id='123'
You must specify the Id of the order when deleting an item from this table.
DELETE FROM Orders WHERE Id='123'
Name | Type | ReadOnly | References | Description |
Id [KEY] | Long | True |
A unique numeric identifier for the order. | |
Number | Long | True |
A unique identifier for the order, used by the shop owner and customer. | |
CustomerId | Long | False |
Customers.Id |
A unique numeric identifier for the customer. |
CustomerFirstName | String | False |
The first name of the customer. | |
CustomerLastName | String | False |
The last name of the customer. | |
Currency | String | False |
The three letter code (ISO 4217) for the currency used for the payment. | |
String | False |
The customer's email address. | ||
Name | String | True |
The customer's order name as represented by a number. | |
TotalDiscounts | Decimal | True |
The total amount of the discounts to be applied to the price of the order. | |
TotalOrderItemsPrice | Decimal | True |
The sum of all the prices of all the items in the order. | |
TotalPrice | Decimal | True |
The sum of all the prices of all the items in the order, taxes and discounts included. | |
SubtotalPrice | Decimal | True |
Price of the order before shipping and taxes. | |
TotalTax | Decimal | False |
The sum of all the taxes applied to the order. | |
TotalWeight | Integer | True |
The sum of all the weights of the line items in the order, in grams. | |
BrowserIp | String | True |
The IP address of the browser used by the customer when placing the order. | |
BuyerAcceptsMarketing | Boolean | False |
Indicates whether or not the person who placed the order would like to receive email updates from the shop. | |
CancelReason | String | True |
The reason why the order was canceled. | |
CartToken | String | True |
Unique identifier for a particular cart that is attached to a particular order. | |
OrderStatusUrl | String | True |
The URL pointing to the order status web page. | |
Token | String | True |
Unique identifier for a particular order. | |
TaxesIncluded | Boolean | False |
States whether or not taxes are included in the order subtotal. | |
SourceName | String | True |
Where the order originated. | |
ReferringSite | String | True |
The website that the customer clicked on to come to the shop. | |
ProcessingMethod | String | True |
States the type of payment processing method. | |
Phone | String | False |
The customer's phone number. | |
Note | String | False |
The text of an optional note that a shop owner can attach to the order. | |
LocationId | Long | True |
The unique numeric identifier for the physical location at which the order was processed. | |
LandingSite | String | True |
The URL for the page where the buyer landed when entering the shop. | |
Tags | String | False |
Additional short descriptors. | |
FulfillmentStatus | String | False |
The fulfillment status of the order. | |
FinancialStatus | String | False |
The financial status of the order. | |
CustomerLocale | String | True |
The customer locale of the order. | |
InvoiceSentAt | Datetime | True |
This auto-generated property is the date and time when the invoice was emailed to the customer. Only available for draft orders. | |
InvoiceUrl | String | True |
The URL for the invoice. Only available for draft orders. | |
TaxExempt | Boolean | True |
States whether or not taxes are exempt for this order. Only available for draft orders. | |
DiscountCodes | String | True |
Applicable discount codes that can be applied to the order. | |
LineAggregate | String | False |
A JSON aggregate of line items associated with the order. | |
TaxAggregate | String | False |
A JSON aggregate of tax line items associated with the order. Note, these taxes are applied on the order, not on individual items. If you want them to be applied on individual items, use them in LineAggregate. Tax lines must be associated with either order or line item but not both. | |
ShippingAggregate | String | False |
A JSON aggregate of shipping line items associated with the order. | |
NoteAttributesAggregate | String | False |
A JSON aggregate of note attributes associated with the order. | |
DiscountApplicationsAggregate | String | True |
An ordered list of stacked discount applications. | |
BillingAddressFirstName | String | False |
The first name of the person associated with the payment method. | |
BillingAddressLastName | String | False |
The last name of the person associated with the payment method. | |
BillingAddressAddress1 | String | False |
The street address of the billing address. | |
BillingAddressAddress2 | String | False |
An optional additional field for the street address of the billing address. | |
BillingAddressPhone | String | False |
The phone number at the billing address. | |
BillingAddressCity | String | False |
The city of the billing address. | |
BillingAddressCompany | String | False |
The company of the person associated with the billing address. | |
BillingAddressZip | String | False |
The zip or postal code of the billing address. | |
BillingAddressProvince | String | False |
The name of the state or province of the billing address. | |
BillingAddressCountry | String | False |
The name of the country of the billing address. | |
BillingAddressLatitude | Double | False |
The latitude of the billing address. | |
BillingAddressLongitude | Double | False |
The longitude of the billing address. | |
BillingAddressName | String | False |
The full name of the person associated with the payment method. | |
BillingAddressCountryCode | String | False |
The two-letter code for the country of the billing address. | |
BillingAddressProvinceCode | String | False |
The two-letter abbreviation of the state or province of the billing address. | |
BillingAddressDefault | Boolean | False |
Whether this address is the default one or not. | |
ShippingAddressFirstName | String | False |
The first name of the person associated with the shipping method. | |
ShippingAddressLastName | String | False |
The last name of the person associated with the shipping method. | |
ShippingAddressAddress1 | String | False |
The street address of the shipping address. | |
ShippingAddressAddress2 | String | False |
An optional additional field for the street address of the shipping address. | |
ShippingAddressPhone | String | False |
The phone number at the shipping address. | |
ShippingAddressCity | String | False |
The city of the shipping address. | |
ShippingAddressCompany | String | False |
The company of the person associated with the shipping address. | |
ShippingAddressZip | String | False |
The zip or postal code of the shipping address. | |
ShippingAddressProvince | String | False |
The name of the state or province of the shipping address. | |
ShippingAddressCountry | String | False |
The name of the country of the shipping address. | |
ShippingAddressLatitude | Double | False |
The latitude of the shipping address. | |
ShippingAddressLongitude | Double | False |
The longitude of the shipping address. | |
ShippingAddressName | String | False |
The full name of the person associated with the shipping method. | |
ShippingAddressCountryCode | String | False |
The two-letter code for the country of the shipping address. | |
ShippingAddressProvinceCode | String | False |
The two-letter abbreviation of the state or province of the shipping address. | |
ShippingAddressDefault | Boolean | False |
Whether this address is the default one or not. | |
AppliedDiscountTitle | String | False |
The title of the applied discount for this order. Only available for draft orders. | |
AppliedDiscountDescription | String | False |
The description of the applied discount for order. Only available for draft orders. | |
AppliedDiscountValue | Decimal | False |
The value of the applied discount for this order. Only available for draft orders. | |
AppliedDiscountValueType | String | False |
The value type of the applied discount for order. Only available for draft orders. | |
AppliedDiscountAmount | Decimal | False |
The amount of the applied discount for this order. Only available for draft orders. | |
PaymentTermsAmount | Long | True |
The amount that is owed according to the payment terms. | |
PaymentTermsCurrency | String | True |
The presentment currency for the payment. | |
PaymentTermsPaymentTermsName | String | True |
The name of the selected payment terms template for the order. | |
PaymentTermsPaymentTermsType | String | True |
The type of selected payment terms template for the order. | |
PaymentTermsDueInDays | Integer | True |
The number of days between the invoice date and due date that is defined in the selected payment terms template. | |
PaymentTermsPaymentSchedules | String | True |
An array of schedules associated to the payment terms. | |
ProcessedAt | Datetime | True |
The date and time when the order was imported, in ISO 8601 format. | |
CreatedAt | Datetime | True |
The date and time when the order was created. | |
ClosedAt | Datetime | True |
The date and time when the order was closed. | |
CancelledAt | Datetime | True |
The date and time when the order was canceled. | |
UpdatedAt | Datetime | True |
The date and time when the order was last modified. | |
SendReceipt | Boolean | False |
Determines whether an order confirmation will be sent to the customer. The default value is false. | |
SendFulfillmentReceipt | Boolean | False |
Determines whether a fulfillment confirmation will be sent to the customer. The default value is false. | |
InventoryBehaviour | String | False |
Determines which inventory updating behavior is used. The following values are available: default, decrement_ignoring_policy, decrementobeying_policy The allowed values are default, decrement_ignoring_policy, decrementobeying_policy. The default value is default. | |
Operation | String | False |
An operation to apply to the Order. Valid values for order: Close, Open, or Cancel. The allowed values are Close, Open, Cancel. | |
TotalShippingPriceSetShopMoneyAmount | Double | True |
The amount of the shop money in the total shipping price set. | |
TotalShippingPriceSetShopMoneyCurrencyCode | String | True |
The currency code of the shop money in the total shipping price set. |
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
Name | Type | Description |
Status | String |
Filter orders by their status. Valid values are: open: show only open orders, closed: show only closed orders, cancelled: show only canceled orders, any: show orders of any status,default: open. |
Create and query transactions.
The Cloud uses the Shopify API to process search criteria that refer to the OrderId column. The Cloud processes other filters client-side within the Cloud.
The Shopify APIs requires the search criteria to contain OrderId. For example, the following query is processed server side.
SELECT * FROM OrderTransactions WHERE OrderId = '123'
When performing a query without a filter or a filter other then the OrderId column, the Cloud will retrieve the entire list of transactions and perform the filters client-side.
The Shopify API requires that an OrderId be specified when retrieving transactions. Therefore to retrieve all the transactions, the Cloud will first retrieve all the OrderIds
from the Orders table (essentially performing the query: SELECT Id FROM Orders). Once the OrderId values have been retrieved, the Cloud will issue individual requests to the
Shopify API to retrieve all the transactions for each OrderId to build the result set.
SELECT * FROM OrderTransactions
SELECT * FROM OrderTransactions WHERE Kind='Capture'
You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false. Other search criteria will be ignored and an unfiltered response will be returned.
You must specify the OrderId and Kind when inserting a transaction.
Capture a specified amount on a previously authorized order.
INSERT INTO OrderTransactions(OrderId, Amount, Kind) VALUES('123', 10.25, 'Capture')
Capture a previously authorized order for the full amount.
INSERT INTO OrderTransactions(OrderId, Kind) VALUES('123', 'Capture')
Name | Type | ReadOnly | References | Description |
Id [KEY] | Long | True |
A unique numeric identifier for the transaction. | |
OrderId | Long | False |
Orders.Id |
A unique numeric identifier for the order. |
UserId | Long | True |
The unique identifier for the user. | |
LocationId | Long | True |
The ID of the physical location where the transaction was processed. | |
Amount | Decimal | False |
The amount of money that the transaction was for. | |
Authorization | String | True |
The authorization code associated with the transaction. | |
Currency | String | True |
The three letter code (ISO 4217) for the currency used for the payment. | |
DeviceId | String | True |
The unique identifier for the device. | |
Gateway | String | False |
The name of the gateway the transaction was issued through. | |
SourceName | String | True |
The origin of the transaction. | |
Kind | String | False |
The kind of transaction. Can be any of the following: Authorization, Capture, Sale, Void, Refund. The allowed values are Authorization, Capture, Sale, Void, Refund. | |
Message | String | True |
The message associated with this transaction. | |
ErrorCode | String | True |
A standardized error code, independent of the payment provider. | |
Status | String | False |
The status of the transaction. | |
Test | Boolean | True |
The option to use the transaction for testing purposes. | |
AVSResultCode | String | True |
The Response code from the address verification system. | |
CreditCardBin | String | True |
The issuer identification number (IIN). | |
CVVResultCode | String | True |
The Response code from the credit card company. | |
CreditCardNumber | String | True |
The customer's credit card number, with most of the leading digits redacted with Xs. | |
CreditCardCompany | String | True |
The name of the company who issued the customer's credit card. | |
CreatedAt | Datetime | True |
The date and time when the customer was created. | |
PaymentsRefundsAttributesStatus | String | True |
The current status of the refund The allowed values are pending, faliure, success, error. | |
PaymentsRefundsAttributesAcquirerReferenceNumber | String | True |
A unique number associated with the transaction that can be used to track the refund. |
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
Name | Type | Description |
TransactionItemAmount | Decimal |
The amount of money that the transaction was for. Use this when inserting transactions into an order or refund. |
TransactionItemGateway | String |
The name of the gateway the transaction was issued through. Use this when inserting transactions into an order or refund. |
TransActionItemKind | String |
The kind of transaction. Can be any of the following: Authorization, Capture, Sale, Void, Refund. Use this when inserting transactions into an order or refund. The allowed values are Authorization, Capture, Sale, Void, Refund. |
TransactionItemStatus | String |
The status of the transaction. Use this when inserting transactions into an order or refund. |
TransactionItemParentId | String |
The parent id of the transaction. Use this when inserting transactions into a refund. |
UpdatedAt | Datetime |
The last time the order is updated. |
Create, read, update or delete pages
The Cloud processes all filters client-side within the Cloud. The following queries are the only ones processed server side:
SELECT * FROM Pages
SELECT * FROM Pages WHERE Id='123'
You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.
You must specify the Title to create a Page.
INSERT INTO Pages(title) VALUES ('new Page')
You must specify the id to update a Page. For example:
UPDATE Pages SET author='Harry' WHERE Id = '77171130'
You must specify the Id of the Page to delete it.
DELETE FROM Pages WHERE Id = '555695'
Name | Type | ReadOnly | References | Description |
Id [KEY] | Long | True |
The unique numeric identifier for the page. | |
AdminGraphqlApiId | String | True |
Displays the Admin Graphql API id. | |
Author | String | False |
The name of the person who created the page. | |
BodyHtml | String | False |
The text content of the page, complete with HTML markup. | |
CreatedAt | Datetime | True |
The date and time (ISO 8601 format) when the page was created. | |
Handle | String | False |
A unique, human-friendly string for the page, generated automatically from its title. In online store themes, the Liquid templating language refers to a page by its handle. | |
Metafields | String | True |
Additional information attached to the Page object. | |
PublishedAt | Datetime | False |
The date and time (ISO 8601 format) when the page was published. Returns null when the page is hidden. | |
ShopId | Long | True |
The ID of the shop to which the page belongs. | |
TemplateSuffix | String | False |
The suffix of the Liquid template being used. | |
Title | String | False |
The page's title. | |
UpdatedAt | Datetime | True |
The date and time (ISO 8601 format) when the page was last updated. |
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
Name | Type | Description |
published_status | String |
Filter comments by their publish status. Valid values are: published, unpublished and any ; default: any. |
Create, update, delete, and query price rules.
The Cloud processes all filters client-side within the Cloud. The following queries are the only ones processed server side:
SELECT * FROM PriceRules WHERE Id = '123'
You must specify the Title, TargetType, TargetSelection, ValueType, Value, CustomerSelection, StartsAt, and AllocationMethod columns to create a price rule. For example:
INSERT INTO PriceRules (Title,TargetType,TargetSelection,ValueType,Value,CustomerSelection,StartsAt,AllocationMethod) VALUES ('SUMMERSALE10OFF', 'line_item', 'all', 'fixed_amount', '-10.0', 'all', '2017-01-19T17:59:10Z','across')
You must specify the Id to update a price rule. For example:
UPDATE PriceRules SET Title = 'SUMMERSALE10OFF' WHERE Id = '123'
You must specify the Id of the price rule to delete it.
DELETE FROM PriceRules WHERE Id = '123'
Name | Type | ReadOnly | References | Description |
Id [KEY] | Long | True |
A unique numeric identifier for the price rule. | |
AllocationMethod | String | False |
The allocation method of the price rule. Valid values: each: The discount is applied to each of the entitled items. across: The calculated discount amount will be applied across the entitled items. The allowed values are each, across. | |
CustomerSelection | String | False |
The customer selection for the price rule. Valid values: all: The price rule is valid for all customers. prerequisite: The customer must either belong to one of the customer saved searches specified by PrerequisiteSavedSearchIds. The allowed values are all, prerequisite. | |
EntitledCollectionIds | String | False |
A list of Ids of collections whose products will be eligible to the discount. It can be used only with TargetType set to line_item and TargetSelection set to entitled. It can't be used in combination with EntitledProductIds or EntitledVariantIds. | |
EntitledCountryIds | String | False |
A list of Ids of shipping countries that will be entitled to the discount. It can be used only with TargetType set to shipping_line and TargetSelection set to entitled. | |
EntitledProductIds | String | False |
A list of Ids of products that will be entitled to the discount. It can be used only with TargetType set to line_item and TargetSelection set to entitled. | |
EntitledVariantIds | String | False |
A list of Ids of product variants that will be entitled to the discount. It can be used only with TargetType set to line_item and TargetSelection set to entitled. | |
OncePerCustomer | Boolean | False |
Whether the generated discount code will be valid only for a single use per customer. This is tracked using customer Id. | |
PrerequisiteCustomerIds | String | False |
A list of customer Ids which for the price rule to be applicable, the customer must match one of the specified customers. If PrerequisiteCustomerIds is populated, then PrerequisiteSavedSearchIds must be empty. | |
PrerequisiteQuantityRange | String | False |
The minimum number of items for the price rule to be applicable. It has the following property: greater_than_or_equal_to: The quantity of an entitled cart item must be greater than or equal to this value. | |
PrerequisiteSavedSearchIds | String | False |
A list of customer saved search Ids. For the price rule to be applicable, the customer must be in the group of customers matching a customer saved search. If PrerequisiteSavedSearchIds is populated, then PrerequisiteCustomerIds must be empty. | |
PrerequisiteShippingPriceRange | String | False |
The maximum shipping price for the price rule to be applicable. It has the following property: less_than_or_equal_to: The shipping price must be less than or equal to this value. | |
PrerequisiteSubtotalRange | String | False |
The minimum subtotal for the price rule to be applicable. It has the following property: greater_than_or_equal_to: The subtotal of the entitled cart items must be greater than or equal to this value for the discount to apply. | |
TargetSelection | String | False |
The target selection method of the price rule. Valid values: all: The price rule applies the discount to all line items in the checkout, entitled: The price rule applies the discount to selected entitlements only. | |
TargetType | String | False |
The target type that the price rule applies to. Valid values: line_item: The price rule applies to the cart's line items, shipping_line: The price rule applies to the cart's shipping lines. | |
UsageLimit | Integer | False |
The maximum number of times the price rule can be used, per discount code. | |
PrerequisiteProductIds | String | False |
List of product ids that will be a prerequisites for a Buy X Get Y type discount. The PrerequisiteProductIds can be used only with: TargetType set to line_item, TargetSelection set to entitled, AllocationMethod set to each and PrerequisiteToEntitlementQuantityRatio defined. Caution: If a product variant is included in PrerequisiteVariantIds, then PrerequisiteProductIds can't include the ID of the product associated with that variant. | |
PrerequisiteVariantIds | String | False |
List of variant ids that will be a prerequisites for a Buy X Get Y type discount. The EntitledVariantIds can be used only with: TargetType set to line_item, TargetSelection set to entitled, AllocationMethod set to each and PrerequisiteToEntitlementQuantityRatio defined. Caution: If a product is included in PrerequisiteProductIds, then PrerequisiteVariantIds can't include the ID of any variants associated with that product. | |
PrerequisiteCollectionIds | String | False |
List of collection ids that will be a prerequisites for a Buy X Get Y discount. The EntitledCollectionIds can be used only with: TargetType set to line_item, TargetSelection set to entitled, AllocationMethod set to each and PrerequisiteToEntitlementQuantityRatio defined. Cannot be used in combination with PrerequisiteProductIds or PrerequisiteVariantIds. | |
Value | Decimal | False |
The value of the price rule. If if the value of target_type is shipping_line, then only -100 is accepted. The value must be negative. | |
ValueType | String | False |
The value type of the price rule. Valid values: fixed_amount: Applies a discount of value as a unit of the store's currency. For example, if value is -30 and the store's currency is USD, then $30 USD is deducted when the discount is applied, percentage: Applies a percentage discount of value. For example, if value is -30, then 30% will be deducted when the discount is applied. If TargetType is shipping_line, then only percentage is accepted. | |
PrerequisiteToEntitlementQuantityRatio | String | False |
Buy/Get ratio for a Buy X Get Y discount. prerequisite_quantity defines the necessary 'buy' quantity and entitled_quantity the offered 'get' quantity. The PrerequisiteToEntitlementQuantityRatio can be used only with: ValueType set to percentage, TargetType set to line_item, TargetSelection set to entitled, AllocationMethod set to each, PrerequisiteProductIds or PrerequisiteVariantIds or PrerequisiteCollectionIds defined and EntitledProductIds or EntitledVariantIds or EntitledCollectionIds defined. | |
Images | String | False |
A list of image objects. | |
ProductType | String | False |
A categorization that a product can be tagged with, commonly used for filtering and searching. | |
Options | String | True |
Custom product property names. | |
PublishedScope | String | False |
Indicates whether the product is published to the Point of Sale channel. | |
Tags | String | False |
A categorization that a product can be tagged with. Use commas to separate categories. | |
TemplateSuffix | String | True |
The suffix of the liquid template being used. | |
Title | String | False |
The name of the product. | |
Vendor | String | False |
The name of the vendor of the product. | |
StartsAt | Datetime | False |
The date and time when the price rule starts. | |
EndsAt | Datetime | False |
The date and time when the price rule ends. Must be after StartsAt. | |
PublishedAt | Datetime | True |
The date and time when the product was published to the Online Store channel. | |
CreatedAt | Datetime | True |
The date and time when the price rule was created. | |
UpdatedAt | Datetime | True |
The date and time when the price rule was last modified. |
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
Name | Type | Description |
Published | Boolean |
Set to true to publish the product or to false to not publish it |
MetafieldsGlobalTitleTag | String |
The name of the product, to be used for SEO purposes. |
MetafieldsGlobalDescriptionTag | String |
The description of the product, to be used for SEO purposes. |
Query, Update and Delete Product Images
The Cloud uses the Shopify API to process search criteria that refer to the ProductId and ImageId. The supported SQL operators are '=' for ProductId and '=' for ImageId. The Cloud processes other filters client-side within the Cloud. For example, the following queries are processed server side.
SELECT * FROM ProductImages WHERE ProductId = '123'
SELECT * FROM ProductImages WHERE ProductId = '123' AND ImageId = '2342'
To create a new product image with image data as attachment
INSERT INTO ProductImages (productid,filename,attachment) VALUES ('64146735127','test.png','iVBORw0KGgoAAAANSUhEUgAAAAUAAAAFCAYAAACNbyblAAAAHElEQVQI12P4//8/w38GIAXDIBKE0DHxgljNBAAO9TXL0Y4OHwAAAABJRU5ErkJggg==')")
To create a new product image using a source URL that will be downloaded by Shopify
INSERT INTO ProductImages (productid,filepath) VALUES ('64146735127','http://example.com/rails_logo.gif')")
To create a new product image and make it the main image
INSERT INTO ProductImages (productid,position,filename,filepath) VALUES ('64146735127',1,'test.png','http://example.com/rails_logo.gif')")
To create a new product image and attach it to product variants
INSERT INTO ProductImages (productid,variantids,filename,attachment) VALUES ('64146735127','[808950810,457924702]','test.png','iVBORw0KGgoAAAANSUhEUgAAAAUAAAAFCAYAAACNbyblAAAAHElEQVQI12P4//8/w38GIAXDIBKE0DHxgljNBAAO9TXL0Y4OHwAAAABJRU5ErkJggg==')")
To update a product image you must specify its ProductId and ImageId.
To change the position and alt tag content for an image
UPDATE ProductImages SET Position = 1, Alt = 'This is the main image' WHERE ProductId = '123' AND ImageId = '2342'
To add it to product variants for an image
UPDATE ProductImages SET VariantIds = '[808950810,457924702]' WHERE ProductId = '123' AND ImageId = '2342'
To delete a product image you must specify its Id.
DELETE FROM ProductImages WHERE ProductId = '123' AND ImageId = '2342'
Name | Type | ReadOnly | References | Description |
ImageId [KEY] | Long | True |
A unique numeric identifier for the product image. | |
ProductId | Long | True |
Products.Id |
The id of the product associated with the image. |
VariantIds | String | False |
The array of variant ids associated with the image. | |
Position | Integer | False |
The order of the product image in the list. | |
FilePath | String | False |
Full path to the image. Can be locally or online. Example: http://example.com/rails_logo.gif. Either this or Base64Content are required. | |
Width | Integer | True |
The width of the image. | |
Height | Integer | True |
The height of the image. | |
CreatedAt | Datetime | True |
The datetime when the image was created. | |
UpdatedAt | Datetime | True |
The datetime when the image was updated. | |
Alt | String | False |
The Alt content of the image. | |
Src | String | False |
Specifies the location of the product image. This parameter supports URL filters that you can use to retrieve modified copies of the image. |
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
Name | Type | Description |
FileName | String |
The name that the image will be displayed in the application. |
Attachment | String |
Base64 encoded bytes of the image. Either this or FilePath are required. |
Query and delete product listings.
The Cloud uses the Shopify API to process search criteria that refer to the ProductId, UpdatedAt, and Handle columns. The supported SQL operators are '=' and 'IN' for ProductId, '=' for Handle, and '>' for the UpdatedAt column. The Cloud processes other filters client-side within the Cloud. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.
For example, the following queries are processed server side:
SELECT * FROM ProductListings
SELECT * FROM ProductListings WHERE ProductId='123'
SELECT * FROM ProductListings WHERE ProductId IN ('123','456')
SELECT * FROM ProductListings WHERE UpdatedAt>'2018-02-21'
SELECT * FROM ProductListings WHERE Handle='ipod-nano'
Delete a product listing to unpublish a product from your app (ProductId is required):
DELETE FROM ProductListings WHERE Productid=512433520663
Name | Type | ReadOnly | References | Description |
ProductId [KEY] | Long | False |
The unique identifer of the product this listing is for. The primary key for this resource. | |
Title | String | False |
The name of the product. | |
BodyHtml | String | False |
The description of the product, complete with HTML formatting. | |
Handle | String | True |
A human-friendly unique string for the Product automatically generated from its title. | |
Variants | String | True |
A list of variant objects, each one representing a slightly different version of the product. | |
Images | String | False |
A list of image objects, each one representing an image associated with the product. | |
ProductType | String | False |
A categorization that a product can be tagged with, commonly used for filtering and searching. | |
Options | String | True |
Custom product property names like 'Size', 'Color', and 'Material'. | |
Tags | String | False |
A categorization that a product can be tagged with, commonly used for filtering. | |
Vendor | String | False |
The name of the vendor of the product. | |
PublishedAt | Datetime | True |
The date and time when the product was published to the Online Store channel. | |
CreatedAt | Datetime | True |
The date and time when the product was created. | |
UpdatedAt | Datetime | True |
The date and time when the product was last modified. |
Create, update, delete, and query products.
The Cloud uses the Shopify API to process search criteria that refer to the following columns: Id, Title, Vendor, Handle, ProductType, PublishedStatus, CreatedAt, PublishedAt, and UpdatedAt. The Cloud processes other filters client-side within the Cloud. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.
For example, the following queries are processed server side.
SELECT * FROM Products
SELECT * FROM Products WHERE Id = '123'
SELECT * FROM Products WHERE Id IN ('123','456')
SELECT * FROM Products WHERE CreatedAt > '2017-10-25'
SELECT * FROM Products WHERE Title = 'just a title'
SELECT * FROM Products WHERE Vendor = 'just a vendor'
Create a new product with the default product variant.
INSERT INTO Products(Title, BodyHtml, Vendor, ProductType, Tags) VALUES('Burton Custom Freestyle 151', 'Good snowboard!', 'Burton', 'Snowboard', 'Barnes & Noble, Johns Fav, Big Air')
Create a new unpublished product.
INSERT INTO Products(Title, BodyHtml, Vendor, ProductType, Tags, Published) VALUES('Burton Custom Freestyle 151', 'Good snowboard!', 'Burton', 'Snowboard', 'Barnes & Noble, Johns Fav, Big Air', false)
To update a product you must specify its Id.
UPDATE Products SET Published=true WHERE Id='123'
UPDATE Products SET MetafieldsGlobalTitleTag='Brand new title', MetafieldsGlobalDescriptionTag='Brand new description' WHERE Id='123'
To delete a product you must specify its Id.
DELETE FROM Products WHERE Id='123'
Name | Type | ReadOnly | References | Description |
Id [KEY] | Long | True |
A unique numeric identifier for the product. | |
BodyHtml | String | False |
The description of the product, complete with HTML formatting. | |
Handle | String | True |
A human-friendly unique string for the Product automatically generated from its title. | |
Images | String | False |
A list of image objects. | |
ProductType | String | False |
A categorization that a product can be tagged with, commonly used for filtering and searching. | |
Options | String | True |
Custom product property names. | |
PublishedScope | String | False |
Indicates whether the product is published to the Point of Sale channel. | |
Tags | String | False |
A categorization that a product can be tagged with. Use commas to separate categories. | |
Status | String | False |
The status of the product. The allowed values are active, archived, draft. | |
TemplateSuffix | String | True |
The suffix of the liquid template being used. | |
Title | String | False |
The name of the product. | |
Vendor | String | False |
The name of the vendor of the product. | |
PublishedAt | Datetime | True |
The date and time when the product was published to the Online Store channel. | |
CreatedAt | Datetime | True |
The date and time when the product was created. | |
UpdatedAt | Datetime | True |
The date and time when the product was last modified. |
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
Name | Type | Description |
PublishedStatus | String |
Return products by their published status: published: show only published products;unpublished: show only unpublished products; any: show all products. |
Published | Boolean |
Set to true to publish the product or to false to not publish it |
MetafieldsGlobalTitleTag | String |
The name of the product, to be used for SEO purposes. |
MetafieldsGlobalDescriptionTag | String |
The description of the product, to be used for SEO purposes. |
Query and delete product listings.
The Cloud uses the Shopify API to process search criteria that refer to the Id and ProductId column. The Cloud processes other filters client-side within the Cloud. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.
For example, the following queries are processed server side.
SELECT * FROM ProductVariants
SELECT * FROM ProductVariants WHERE Id = '123'
SELECT * FROM ProductVariants WHERE Id IN ('123','456')
SELECT * FROM ProductVariants WHERE ProductId = '456'
You must specify the ProductId to insert a product variant.
INSERT INTO ProductVariants(ProductId, Option1, Price) VALUES('123', 'Yellow', 3.5)
You must specify the Id of the variant to edit a product variant.
Update the title and price of an existing variant.
UPDATE ProductVariants SET Option1='Pink', Price=99.99 WHERE Id='123'
You must specify the ProductId and the Id of the variant to delete a product variant.
DELETE FROM ProductVariants WHERE Id='123' AND ProductId='456'
Name | Type | ReadOnly | References | Description |
Id [KEY] | Long | True |
A unique numeric identifier for the product variant. | |
ProductId | Long | False |
A unique numeric identifier for the product this variant belongs to. | |
InventoryItemId | String | False |
The unique identifier for the inventory item, which is used in the Inventory API to query for inventory information. | |
ImageId | Long | False |
A unique numeric identifier for the image this variant belongs to. | |
Price | Decimal | False |
The price of the product variant. | |
CompareAtPrice | String | False |
The competitors prices for the same item. | |
Title | String | False |
The name of the product. | |
Grams | Integer | False |
The weight of the product variant in grams. | |
Barcode | String | False |
A human-friendly unique string for the Product automatically generated from its title. | |
Weight | Double | False |
The weight of the product variant in the unit system specified. | |
WeightUnit | String | True |
The unit of measurement that applies to the product's variant weight. | |
FulfillmentService | String | False |
Service which is doing the fulfillment. | |
InventoryManagement | String | False |
Specifies whether or not Shopify tracks the number of items in stock for this product variant. | |
InventoryPolicy | String | True |
Specifies whether or not customers are allowed to place an order for a product variant when it's out of stock. Valid values are deny or continue. | |
InventoryQuantity | Integer | True |
The number of items in stock for this product variant. Default value 1. | |
Option1 | String | False |
Custom properties that a shop owner can use to define product variants. | |
Option2 | String | False |
Custom properties that a shop owner can use to define product variants. | |
Option3 | String | False |
Custom properties that a shop owner can use to define product variants. | |
Position | Integer | False |
The order of the product variant in the list of product variants. | |
RequiresShipping | Boolean | False |
Specifies whether or not a customer needs to provide a shipping address when placing an order for this product variant. | |
Sku | String | False |
A unique identifier for the product in the shop. | |
Taxable | Boolean | False |
Specifies whether or not a tax is charged when the product variant is sold. | |
CreatedAt | Datetime | True |
The date and time when the product variant was created. | |
UpdatedAt | Datetime | True |
The date and time when the product was last modified. |
Query and update information regarding different provinces.
The Cloud uses the Shopify API to process search criteria that refer to the ProductId, UpdatedAt, and Handle columns. The supported SQL operators are '=' and 'IN' for ProductId, '=' for Handle, and '>' for the CreatedAt column. The Cloud processes other filters client-side within the Cloud. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria that refers to other columns will cause an error.
For example, the following queries are processed server side:
SELECT * FROM Provinces
SELECT * FROM Provinces WHERE CountryId='123'
You must specify the CountryId and Id to update a province. For example:
UPDATE Provinces Set Tax='3.4' WHERE CountryId='123' AND Id='456'
Name | Type | ReadOnly | References | Description |
Id [KEY] | Long | True |
The ID for the province. | |
CountryId [KEY] | Long | True |
Countries.Id |
The ID for the country that the province belongs to. |
Name | String | False |
The full name of the province. | |
Code | String | False |
The standard abbreviation for the province. | |
Tax | Decimal | False |
The sales tax rate to be applied to orders made by customers from this province.. | |
TaxPercentage | Double | False |
The province's tax in percent format. | |
TaxName | String | False |
The name of the tax for this province. | |
TaxType | String | False |
The tax type. Valid values: normal, null, or harmonized. If the value is harmonized, then the tax is compounded of the provincial and federal sales taxes. | |
ShippingZoneId | Long | True |
The ID for the shipping zone that the province belongs to. |
Create, update, delete, and query Recurring Application Charges.
The Cloud processes all filters client-side within the Cloud. The following queries are the only ones processed server side:
SELECT * FROM RecurringApplicationCharges
SELECT * FROM RecurringApplicationCharges WHERE Id = '123'
You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.
UPDATE RecurringApplicationCharges SET Test = 'true', Name = 'cd' WHERE Id ='2'
You must specify the Id of the recurring application charge to delete it.
DELETE From RecurringApplicationCharges WHERE Id ='2'
Name | Type | ReadOnly | References | Description |
Id [KEY] | Long | False |
The ID of the recurring application charge. | |
Name | String | False |
The name of the recurring application charge. | |
ApiClientId | String | False |
The API Client Id | |
Price | Decimal | False |
The price of the recurring application charge. The maximum price is 10,000. | |
Status | String | False |
The status of the recurring charge | |
ReturnUrl | String | False |
The URL where the merchant is redirected after accepting the charge. | |
BillingOn | Date | False |
The date and time (ISO 8601 format) when the customer was billed. | |
Test | String | False |
Whether the application charge is a test transaction. | |
CreatedAt | Datetime | False |
The date and time (ISO 8601 format) when the recurring application charge was created. | |
UpdatedAt | Datetime | False |
The date and time (ISO 8601 format) when the recurring application charge was last updated. | |
ActivatedOn | Datetime | False |
The date and time (ISO 8601 format) when the customer activated the recurring application charge. | |
CancelledOn | Datetime | False |
The date and time (ISO 8601 format) when the merchant canceled their recurring application charge. | |
TrialDays | Integer | False |
The number of days that the customer is eligible for a free trial. | |
TrialEndsOn | Datetime | False |
The date and time (ISO 8601 format) when the free trial ends. | |
DecoratedReturnUrl | String | False |
The decorated return url. | |
CappedAmount | String | False |
The limit a customer can be charged for usage based billing. If this property is provided, then you must also provide the terms property. | |
ConfirmationUrl | String | False |
The URL where the merchant accepts or declines the recurring application charge. | |
Terms | String | False |
The terms and conditions of usage based billing charges. Must be present in order to create usage charges, |
Create, read, update or delete redirects.
The Cloud processes all filters client-side within the Cloud. The following queries are the only ones processed server side:
SELECT * FROM Redirects
SELECT * FROM Redirects WHERE Id='123'
You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.
You must specify path and target to create a Redirect.
INSERT INTO Redirects(path,target) VALUES ('/abc.php','/pqr.jsp')
You must specify the id to update a Redirect. For example:
UPDATE Redirects SET Path='/abc.php' WHERE Id = '77171130'
You must specify the Id of the Redirect to delete it.
DELETE FROM Redirects WHERE Id = '555695'
Name | Type | ReadOnly | References | Description |
Id [KEY] | Long | True |
The ID for the redirect. | |
Path | String | False |
The old path to be redirected. | |
Target | String | False |
The target location where the user will be redirected. |
Create and query refunds.
The Cloud uses the Shopify API to process search criteria that refer to the OrderId column. The Cloud processes other filters client-side within the Cloud.
For example, the following queries are processed server side.
If you specify the unique identifier of the Order, then this view will only list refund information concerning that order.
SELECT * FROM Refunds
SELECT * FROM Refunds WHERE OrderId = '179098550295'
SELECT * FROM Refunds WHERE OrderId='179098550295' AND Id='7382073367'
You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false. When SupportEnhancedSQL is false, you must specify at least OrderId. The Shopify APIs require that queries contain this filter. Search criteria that refers to other columns will cause an error.
You must specify the OrderId when inserting a refund.
Calculate a refund for a line item and shipping using aggregates.
INSERT INTO Refunds(OrderId, CalculateRefund, ShippingFullRefund, LineAggregate) VALUES('123', true, true, '[{\"id\":\"123\",\"quantity\":3,\"restock_type\": \"return\",\"location_id\": \"12346\"}]')
Calculate a refund for a line item and shipping using temporary table.
INSERT INTO OrdersItems#Temp (ItemId, ItemQuantity) VALUES ('123', 3)
INSERT INTO Refunds(OrderId, CalculateRefund, ShippingFullRefund, LineAggregate) VALUES('123', true, true, 'OrdersItems#Temp')
Calculate a refund for a partial amount of shipping.
INSERT INTO Refunds(OrderId, CalculateRefund, ShippingAmount) VALUES('123', true, 2.56)
Create a new refund for an order using aggregates.
INSERT INTO Refunds(OrderId, Restock, Notify, Note, ShippingFullRefund, LineAggregate) VALUES('123', true, true, 'Wrong size', true, '[{\"id\":\"123\",\"quantity\":3,\"restock_type\": \"return\"}]')
Create a new refund for an order using temporary table.
INSERT INTO OrdersItems#Temp (ItemId, ItemQuantity) VALUES ('123', 3)
INSERT INTO Refunds(OrderId, Restock, Notify, Note, ShippingFullRefund, LineAggregate) VALUES('123', true, true, 'Wrong size', true, 'OrdersItems#Temp')
Create a new refund for an order and attach a transaction to the refund as well using aggregates.
INSERT INTO Refunds(OrderId, LineAggregate, OrderTransactionAggregate) VALUES('123', '[{\"id\":\"123\",\"quantity\":3,\"restock_type\": \"return\"}]', '[{\"kind\":\"refund\",\"amount\":5.5,\"gateway\":\"bogus\",\"parent_id\":\"789\"}]')
Create a new refund for an order and attach a transaction to the refund as well using temporary table.
INSERT INTO OrdersItems#Temp (ItemId, ItemQuantity) VALUES ('123', 3)
INSERT INTO Transactions#Temp (TransactionItemParentId, TransactionItemAmount, TransactionItemKind, TransactionItemGateway) VALUES ('789', 5.5, 'refund', 'bogus')
INSERT INTO Refunds(OrderId, LineAggregate, OrderTransactionAggregate) VALUES('123', 'OrdersItems#Temp','Transactions#Temp')
Refund a specific amount of shipping using aggregates.
INSERT INTO Refunds(OrderId, ShippingAmount, OrderTransactionAggregate) VALUES('123', 5.3, '[{\"parent_id\":\"456\"}]')
Refund a specific amount of shipping using temporary table.
INSERT INTO Transactions#Temp (TransactionItemParentId) VALUES ('456')
INSERT INTO Refunds(OrderId, ShippingAmount, OrderTransactionAggregate) VALUES('123', 5.3, 'Transactions#Temp')
Name | Type | ReadOnly | References | Description |
Id [KEY] | Long | True |
The unique identifier for the refund. | |
OrderId | Long | False |
Orders.Id |
The id of the order. |
OrderUpdatedAt | Datetime | True |
Orders.UpdatedAt |
The date and time when the order was last modified. |
Note | String | False |
The optional note attached to a refund. | |
Restock | Boolean | True |
Whether or not the line items were added back to the store inventory. | |
UserId | Long | True |
The unique identifier of the user who performed the refund. | |
CreatedAt | Datetime | True |
The date and time when the refund was created. | |
ProcessedAt | Datetime | True |
The date and time when the refund was imported. | |
LineAggregate | String | False |
A JSON aggregate of line items associated with the refund. | |
OrderTransactionAggregate | String | False |
A JSON aggregate of transactions associated with the refund. |
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
Name | Type | Description |
Notify | Boolean |
Whether or not to send a refund notification to the customer. |
DiscrepancyReason | String |
An optional comment, used if there is a discrepancy between calculated and actual refund amounts (one of: restock, damage, customer, other) |
ShippingAmount | Decimal |
Set specific amount of shipping to refund. Takes precedence over FullRefund. |
ShippingFullRefund | Boolean |
Whether or not to to refund all remaining shipping. |
CalculateRefund | Boolean |
Set to true to simply calculate the refund but not process it The default value is false. |
Create, read, update or delete script tags.
The Cloud processes all filters client-side within the Cloud. The following queries are the only ones processed server side:
SELECT * FROM ScriptTags
SELECT * FROM ScriptTags WHERE Id='123'
You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.
You must specify event and src to create a ScriptTag.
INSERT INTO Scripttags(event,src) VALUES ('onload','https://js-aplenty.com/abc.js')
You must specify the id to update a ScriptTag. For example:
UPDATE ScriptTags SET src='https://js-aplenty.com/abc.js' WHERE Id = '77171130'
You must specify the Id of the ScriptTag to delete it.
DELETE FROM ScriptTags WHERE Id = '555695'
Name | Type | ReadOnly | References | Description |
Id [KEY] | Long | True |
The ID for the script tag. | |
Cache | Boolean | False |
Whether the Shopify CDN can cache and serve the script tag. | |
CreatedAt | Datetime | True |
The date and time (ISO 8601) when the script tag was created. | |
DisplayScope | String | False |
The page or pages on the online store where the script should be included. The allowed values are online_store, order_status, all. | |
Event | String | False |
The DOM event that triggers the loading of the script. Valid values: onload. | |
Src | String | False |
The URL of the remote script. | |
UpdatedAt | Datetime | True |
The date and time (ISO 8601) when the script tag was last updated. |
Query, insert, update, or delete information regarding different smart collections.
The Cloud uses the Shopify API to process search criteria that refer to the Id, ProductId, Title, Handle, PublishedStatus, UpdatedAt, PublishedAt columns. The supported SQL operators are '=' and 'IN' for Id, '=' for the ProductId, Title, Handle, and PublishedStatus columns and '>' and '<' for the UpdatedAt and PublishedAt columns. The Cloud processes other filters client-side within the Cloud. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.
For example, the following queries are processed server side.
SELECT * FROM SmartCollections
SELECT * FROM SmartCollections WHERE Id='123'
SELECT * FROM SmartCollections WHERE Id IN ('123','456')
SELECT * FROM SmartCollections WHERE ProductId='123'
SELECT * FROM SmartCollections WHERE Title='Ducks'
SELECT * FROM SmartCollections WHERE Handle='frontpage'
SELECT * FROM SmartCollections WHERE PublishedStatus='published'
SELECT * FROM SmartCollections WHERE UpdatedAt > '2018-05-02'
SELECT * FROM SmartCollections WHERE PublishedAt < '2017-08-15'
You must specify the Title and Rules to create a smart collection. For example:
INSERT INTO SmartCollections (Title, Rules) VALUES ('IPods', '[{"column": "vendor","relation": "equals","condition": "Apple"}]')
You must specify the smart collection Id to update a smart collection. For example:
UPDATE SmartCollections SET BodyHtml='5000 songs in your pocket' WHERE Id='123'
You must specify the Id of the smart collection to delete it.
DELETE FROM SmartCollections WHERE Id ='123'
Name | Type | ReadOnly | References | Description |
Id [KEY] | Long | True |
The ID of the smart collection. | |
Title | String | False |
The name of the smart collection. Maximum length: 255 characters. | |
BodyHtml | String | False |
The description of the smart collection. Includes HTML markup. Many shop themes display this on the smart collection page. | |
Image | String | False |
A JSON aggregate with information regarding the image of smart collection. | |
Rules | String | False |
A JSON aggregate with the list of rules that define what products go into the smart collection. Valid values for each rule field: column: tag, title, type, vendor, variant_price, variant_compare_at_price, variant_weight, variant_inventory, variant_title; relation: equals, greater_than, less_than, starts_with, ends_with, contains; condition: any string | |
Handle | String | False |
A human-friendly unique string for the smart collection. Automatically generated from the title. Used in shop themes by the Liquid templating language to refer to the smart collection. (maximum: 255 characters) | |
Disjunctive | Boolean | False |
Whether the product must match all the rules to be included in the smart collection. Valid values: true: products only need to match one or more of the rules to be included in the smart collection, false: products must match all of the rules to be included in the smart collection. | |
SortOrder | String | False |
The order in which products in the smart collection appear. | |
TemplateSuffix | String | False |
The suffix of the Liquid template that the shop uses. By default, the original template is called product.liquid, and additional templates are called product.suffix.liquid. | |
PublishedScope | String | False |
Whether the smart collection is published to Point of Sale. Valid values: web: The smart collection is published to the shop's online channels and not published to the Point of Sale channel, global: The smart collection is published to both the online channels and the Point of Sale channel. | |
UpdatedAt | Datetime | True |
The date and time when the smart collection was last modified. | |
PublishedAt | Datetime | True |
The date and time when the smart collection was published. Returns null when the collection is hidden. |
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
Name | Type | Description |
ProductId | String |
Show smart collections that include a given product. |
PublishedStatus | String |
Show smart collection with a given published status: published: show only published smart collections, unpublished: show only unpublished smart collections, any: show all smart collections. (default: any). |
Create, read, update or delete themes
The Cloud processes all filters client-side within the Cloud. The following queries are the only ones processed server side:
SELECT * FROM Themes
SELECT * FROM Themes WHERE Id='123'
You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.
You must specify name to create a Theme.
INSERT INTO Themes(name) VALUES ('NewTheme')
You must specify the id to update a Theme. For example:
UPDATE Themes SET name='NewTheme' WHERE Id = '77171130'
You must specify the Id of the Theme to delete it.
DELETE FROM Themes WHERE Id = '555695'
Name | Type | ReadOnly | References | Description |
Id [KEY] | Long | True |
A unique numeric identifier for the theme. | |
AdminGraphqlApiId | String | True |
Displays the Admin Graphql API id. | |
CreatedAt | Datetime | True |
The date and time (ISO 8601 format) when the theme was created. | |
Name | String | False |
The name of the theme. | |
Previewable | Boolean | True |
Whether the theme can currently be previewed. | |
Processing | Boolean | True |
Whether files are still being copied into place for this theme. | |
Role | String | False |
Specifies how the theme is being used within the shop. The allowed values are main, published, demo. | |
ThemeStoreId | Long | True |
A unique identifier applied to Shopify-made themes that are installed from the Shopify Theme Store Theme Store. | |
UpdatedAt | Datetime | True |
The date and time ( ISO 8601 format) when the theme was last updated. |
Create or view Usage Charges for Recurring Application Charges.
The Cloud processes all filters client-side within the Cloud. The following queries are the only ones processed server side:
SELECT * FROM UsageCharges WHERE Id = 123
SELECT * FROM UsageCharges WHERE RecurringApplicationId = 5569619
SELECT * FROM UsageCharges WHERE RecurringApplicationId = 5569619 AND Id = 123
You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.
Name | Type | ReadOnly | References | Description |
Id [KEY] | Long | False |
The ID of the usage charge. | |
Description | String | False |
The name of the usage charge. | |
Price | Decimal | False |
The price of the usage charge. | |
CreatedAt | Datetime | False |
The date and time (ISO 8601 format) when the usage charge was created. | |
BillingOn | Date | False |
The date and time (ISO 8601 format) when the customer is billed. | |
BalanceUsed | Decimal | False |
The used balance | |
BalanceRemaining | Decimal | False |
The remaining balance | |
RiskLevel | Integer | False |
The risk Level |
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
Name | Type | Description |
RecurringApplicationId | Long |
The Id of the RecurringApplication Charge |
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 |
AbandonedCheckouts | Query abandoned checkouts. |
AbandonedCheckoutsItems | Query abandoned checkouts items. |
AssignedFulfillmentOrders | The AssignedFulfillmentOrder resource allows you to retrieve all the fulfillment orders that are assigned to an app at the shop level. |
DiscountApplications | Query note attributes belonging to an order or draft order. |
DraftOrderItemProperties | Query order item properties. |
DraftOrderItems | Query draft order items. |
DraftOrderItemTaxLines | Query draft order items tax lines. |
Events | Retrieve events which have happened in your shop. |
FulfillmentOrders | Query fulfillment orders. |
Locations | Retrieve information regarding store locations. |
NoteAttributes | Query note attributes belonging to an order or draft order. |
OrderDiscountCodes | Query note attributes belonging to an order or draft order. |
OrderItemDiscountAllocations | Query order item discount allocations. |
OrderItemProperties | Query order item properties. |
OrdersItems | Query order items. |
Payouts | Retrieves a list of all payouts ordered by payout date, with the most recent being first. |
PayoutTransactions | Retrieves a list of all balance transactions ordered by processing time, with the most recent being first. |
ProductOptions | Query product options. |
RefundAdjustments | Create and query transactions. |
RefundsItems | Get data on OrdersItems that have been refunded. |
RefundTransactions | Query transactions for Refund Object. |
Reports | To query all the Reports. |
ShippingItemDiscountAllocations | Query Shipping item discount allocations. |
ShippingItems | Query order shipping. |
ShippingZones | Retrieve information regarding shipping zones. |
Shop | Contains general settings and information about the shop. |
TaxItems | Query order taxes. |
Query abandoned checkouts.
The Cloud uses the Shopify API to process search criteria that refer to the CreatedAt and UpdatedAt columns. The Cloud processes other filters client-side within the Cloud. For example, the following queries are processed server side.
SELECT * FROM AbandonedCheckouts
SELECT * FROM AbandonedCheckouts WHERE CreatedAt > '2017-10-25'
You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.
Name | Type | References | Description |
Id | Long | The unique numeric identifier for the order. | |
AbandonedCheckoutUrl | String | The full recovery URL to be sent to a customer to recover their abandoned checkout. | |
BuyerAcceptsMarketing | Boolean | Indicates whether or not the person who placed the order would like to receive email updates from the shop. | |
CancelReason | String | The reason why the order was cancelled. | |
CartToken | String | Unique identifier for a particular cart that is attached to a particular order. | |
Currency | String | The three letter code (ISO 4217) for the currency used for the payment. | |
CustomerId | Long |
Customers.Id | A unique numeric identifier for the customer. |
DiscountCodes | String | Applicable discount codes that can be applied to the order. | |
String | The customer's email address. | ||
Note | String | The text of an optional note that a shop owner can attach to the order. | |
LandingSite | String | The URL for the page where the buyer landed when entering the shop. | |
ReferringSite | String | The website that the customer clicked on to come to the shop. | |
SourceName | String | Where the order originated. | |
SubtotalPrice | Decimal | Price of the order before shipping and taxes. | |
TotalDiscounts | Decimal | The total amount of the discounts to be applied to the price of the order. | |
TotalLineItemsPrice | Decimal | The sum of all the prices of all the items in the order. | |
TotalPrice | Decimal | The sum of all the prices of all the items in the order, taxes and discounts included. | |
TotalTax | Decimal | The sum of all the taxes applied to the order. | |
TotalWeight | Double | The sum of all the weights of the line items in the order, in grams. | |
TaxesIncluded | Boolean | States whether or not taxes are included in the order subtotal. | |
Token | String | Unique identifier for a particular order. | |
BillingAddressFirstName | String | The first name of the person associated with the payment method. | |
BillingAddressLastName | String | The last name of the person associated with the payment method. | |
BillingAddressAdress1 | String | The street address of the billing address. | |
BillingAddressAdress2 | String | An optional additional field for the street address of the billing address. | |
BillingAddressPhone | String | The phone number at the billing address. | |
BillingAddressCity | String | The city of the billing address. | |
BillingAddressCompany | String | The company of the person associated with the billing address. | |
BillingAddressZip | String | The zip or postal code of the billing address. | |
BillingAddressProvince | String | The name of the state or province of the billing address. | |
BillingAddressCountry | String | The name of the country of the billing address. | |
BillingAddressLatitude | Double | The latitude of the billing address. | |
BillingAddressLongitude | Double | The longitude of the billing address. | |
BillingAddressName | String | The full name of the person associated with the payment method. | |
BillingAddressCountryCode | String | The two-letter code for the country of the billing address. | |
BillingAddressProvinceCode | String | The two-letter abbreviation of the state or province of the billing address. | |
BillingAddressDefault | Boolean | Whether this address is the default one or not. | |
ShippingAddressFirstName | String | The first name of the person associated with the shipping address. | |
ShippingAddressLastName | String | The last name of the person associated with the shipping address. | |
ShippingAddressAdress1 | String | The street address of the shipping address. | |
ShippingAddressAdress2 | String | An optional additional field for the street address of the shipping address. | |
ShippingAddressPhone | String | The phone number at the shipping address. | |
ShippingAddressCity | String | The city of the shipping address. | |
ShippingAddressCompany | String | The company of the person associated with the shipping address. | |
ShippingAddressZip | String | The zip or postal code of the shipping address. | |
ShippingAddressProvince | String | The name of the state or province of the shipping address. | |
ShippingAddressCountry | String | The name of the country of the shipping address. | |
ShippingAddressLatitude | Double | The latitude of the shipping address. | |
ShippingAddressLongitude | Double | The longitude of the shipping address. | |
ShippingAddressName | String | The full name of the person associated with the shipping address. | |
ShippingAddressCountryCode | String | The two-letter code for the country of the shipping address. | |
ShippingAddressProvinceCode | String | The two-letter abbreviation of the state or province of the shipping address. | |
ShippingAddressDefault | Boolean | Whether this address is the default one or not. | |
ClosedAt | Datetime | The date and time when the order was closed. | |
CompletedAt | Datetime | ||
CreatedAt | Datetime | The date and time when the order was created. | |
UpdatedAt | Datetime | The date and time when the order was last modified. |
Query abandoned checkouts items.
The Cloud uses the Shopify API to process search criteria that refer to the CreatedAt and UpdatedAt columns. The Cloud processes other filters client-side within the Cloud. For example, the following queries are processed server side.
SELECT * FROM AbandonedCheckoutsItems
SELECT * FROM AbandonedCheckoutsItems WHERE CreatedAt > '2017-10-25'
You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.
Name | Type | References | Description |
AbandonedCheckoutId | Long | The unique numeric identifier for the order. | |
ProductId | Long |
Products.Id | The product ID od the item. |
ItemVariantId | Long | The product variant ID of item. | |
ItemTitle | String | The title of the product. | |
ItemQuantity | Integer | The number of products that were purchased. | |
ItemPrice | Decimal | The price of the item in presentment currency. | |
ItemGrams | Integer | The weight of the item in grams. | |
SKU | String | A unique identifier for the item in the shop. | |
VariantTitle | String | The title of the product variant. | |
Vendor | String | The name of the item's supplier. | |
FulFillmentsService | String | The fulfillment service provider for the item. | |
CreatedAt | Datetime | The date and time when the order was created. | |
UpdatedAt | Datetime | The date and time when the order was last modified. |
The AssignedFulfillmentOrder resource allows you to retrieve all the fulfillment orders that are assigned to an app at the shop level.
The Cloud uses the Shopify API to process search criteria that refer to the AssignedLocationId and RequestStatus columns. The Cloud processes other filters client-side within the Cloud. For example, the following queries are processed server side.
SELECT * from AssignedFulfillmentOrders WHERE AssignedLocationId IN (1,2)
SELECT * from AssignedFulfillmentOrders WHERE RequestStatus = 'cancellation_requested'
Name | Type | References | Description |
Id [KEY] | Long | The ID of the fulfillment order. | |
ShopId | Long |
Shop.Id | The ID of the shop that's associated with the fulfillment order. |
OrderId | Long |
Orders.Id | The ID of the order that's associated with the fulfillment order. |
AssignedLocationId | Long | The ID of the location that has been assigned to do the work. | |
FulfillmentServiceHandle | String | A unique numeric identifier for the order. | |
RequestStatus | String | The status of the fulfillment. | |
DestinationId | String | The ID of the fulfillment order destination. | |
DestinationAddress1 | String | The first line of the address of the destination. | |
DestinationAddress2 | String | The second line of the address of the destination. | |
DestinationCity | String | The city of the destination. | |
DestinationCompany | String | The company of the destination. | |
DestinationFirstName | String | The first name of the customer at the destination. | |
DestinationLastName | String | The last name of the customer at the destination. | |
DestinationPhone | String | The phone number of the customer at the destination. | |
LineItems | String | Represents line items belonging to a fulfillment order. | |
Status | String | The status of the fulfillment order. | |
Operation | String | An operation to apply to the fulfillment. Complete, Open, or Cancel.
The allowed values are Complete, Open, Cancel. |
Query note attributes belonging to an order or draft order.
The Cloud uses the Shopify API to process search criteria that refer to the OrderId and OrderUpdatedAt columns. The Cloud processes other filters client-side within the Cloud. For example, the following queries are processed server side.
SELECT * FROM DiscountApplications WHERE OrderId = '1'
SELECT * FROM DiscountApplications WHERE OrderId IN ('1', '2')
SELECT * FROM DiscountApplications WHERE OrderUpdatedAt <= '2020-07-20 08:28:03.0'
SELECT * FROM DiscountApplications WHERE OrderUpdatedAt > '2020-07-20 08:28:03.0'
Name | Type | References | Description |
OrderId | Long |
Orders.Id | The id of the order. |
OrderUpdatedAt | Datetime | The date and time when the order was last modified. | |
Type | String | The name of the note attribute. | |
Title | String | The value of the note attribute. | |
Description | String | The value of the note attribute. | |
Value | String | The value of the note attribute. | |
ValueType | String | The value of the note attribute. | |
AllocationMethod | String | The value of the note attribute. | |
TargetSelection | String | The value of the note attribute. | |
TargetType | String | The value of the note attribute. |
Query order item properties.
The Cloud uses the Shopify API to process search criteria that refer to the DraftOrderId column. The supported SQL operators are '=' for DraftOrderId. The Cloud processes other filters client-side within the Cloud. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.
For example, the following queries are processed server side.
SELECT * FROM DraftOrderItemProperties WHERE DraftOrderId = '123'
Name | Type | References | Description |
ItemId | Long |
DraftOrderItems.ItemId | The id of the order item. |
DraftOrderId | Long |
DraftOrders.Id | The id of the draft. |
Name | String | The name of the item property. | |
Value | String | The value of the item property. |
Query draft order items.
The Cloud uses the Shopify API to process search criteria that refer to the DraftOrderId. The supported SQL operators are '=' for DraftOrderId. The Cloud processes other filters client-side within the Cloud. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.
For example, the following queries are processed server side.
SELECT * FROM DraftOrderItems
SELECT * FROM DraftOrderItems WHERE DraftOrderId = '123'
Name | Type | References | Description |
ItemId | Long | The id of the line item. | |
DraftOrderId | Long |
DraftOrders.Id | The id of the draft. |
ProductId | Long |
Products.Id | The ID of the product corresponding to the line item product variant. |
Custom | Boolean | States whether this is a custom line item or a product variant line item | |
FulfillableQuantity | Integer | The amount available to fulfill. | |
FulFillmentsService | String | Service provider responsible for fulfillment. | |
ItemGrams | Integer | The weight of the item in grams. | |
ItemPrice | Decimal | The price of the item before discounts have been applied. | |
ItemQuantity | Integer | The number of products that were purchased. | |
RequiresShipping | Boolean | States whether or not the fulfillment requires shipping. | |
SKU | String | A unique identifier of the item in the fulfillment. | |
ItemTitle | String | The title of the product or variant. | |
ItemVariantId | Long | The id of the product variant. | |
VariantTitle | String | The title of the product variant. | |
Vendor | String | The name of the supplier of the item. | |
Name | String | The name of the product variant. | |
GiftCard | Boolean | States whether or not the product is a gift card. | |
Properties | String | Shows custom properties for this order item. | |
Taxable | Boolean | States whether or not the product was taxable. | |
TaxLines | String | Shows tax lines for this order item. | |
AppliedDiscountTitle | String | The title of the applied discount for this order. Only available for draft orders. | |
AppliedDiscountDescription | String | The description of the applied discount for order. Only available for draft orders. | |
AppliedDiscountValue | Decimal | The value of the applied discount for this order. Only available for draft orders. | |
AppliedDiscountValueType | String | The value type of the applied discount for order. Only available for draft orders. | |
AppliedDiscountAmount | Decimal | The amount of the applied discount for this order. Only available for draft orders. |
Query draft order items tax lines.
Name | Type | References | Description |
ItemId | Long |
DraftOrderItems.ItemId | The id of the draft line item. |
DraftOrderId | Long |
DraftOrders.Id | The id of the draft. |
TaxTitle | String | The name of the tax. | |
TaxRate | Decimal | The rate of tax to be applied. | |
TaxPrice | Decimal | The amount of tax to be charged. |
Retrieve events which have happened in your shop.
The Cloud uses the Shopify API to process search criteria that refer to the Id, ProductId, OrderId, Verb, and CreatedAt columns. The supported SQL operators are '=' for the Id, ProductId, OrderId, and Verb columns and ">" and "<" for CreatedAt. The Cloud processes other filters client-side within the Cloud. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.
For example, the following queries are processed server side:
SELECT * FROM Events
SELECT * FROM Events WHERE Id='123'
SELECT * FROM Events WHERE ProductId='123'
SELECT * FROM Events WHERE OrderId='123'
SELECT * FROM Events WHERE Verb='Confirmed'
SELECT * FROM Events WHERE CreatedAt > '2018-05-02'
Name | Type | References | Description |
Id [KEY] | Long | The ID of the event. | |
Description | String | A human readable description of the event. | |
Message | String | A human readable description of the event. Can contain some HTML formatting. | |
Body | String | A text field containing information about the event. | |
Path | String | A relative URL to the resource the event is for, if applicable. | |
SubjectId | Long | The ID of the resource that generated the event. | |
SubjectType | String | he type of the resource that generated the event. Valid values: Article, Blog, Collection, Comment, Order, Page, Product, ApiPermission. | |
Verb | String | The type of event that occurred. Different resources generate different types of event. | |
Author | String | The author of the event. | |
Arguments | String | Refers to a certain event and its resources. | |
CreatedAt | Datetime | The date and time when the event was created. |
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
Name | Type | Description | |
OrderId | Long | The ID of the Order to retrieve events from. | |
ProductId | Long | The ID of the Product to retrieve events from. |
Query fulfillment orders.
The Cloud uses the Shopify API to process search criteria that refer to the OrderId and Id columns. The Cloud processes other filters client-side within the Cloud. For example, the following queries are processed server side.
SELECT * FROM FulfillmentOrders WHERE OrderId = '1'
SELECT * FROM FulfillmentOrders WHERE Id = '1'
Name | Type | References | Description |
Id [KEY] | Long | An ID for the fulfillment order. | |
ShopId | Long |
Shop.Id | The ID of the shop that's associated with the fulfillment order. |
OrderId [KEY] | Long |
Orders.Id | The ID of the order that's associated with the fulfillment order. |
AssignedLocationId | Long | The ID of the location that has been assigned to do the work. | |
DestinationId | String | The ID of the fulfillment order destination. | |
DestinationAddress1 | String | The first line of the address of the destination. | |
DestinationAddress2 | String | The second line of the address of the destination. | |
DestinationCity | String | The city of the destination. | |
DestinationCompany | String | The company of the destination. | |
DestinationFirstName | String | The first name of the customer at the destination. | |
DestinationLastName | String | The last name of the customer at the destination. | |
DestinationPhone | String | The phone number of the customer at the destination. | |
FullfillAt | Datetime | The datetime (in UTC) when the fulfillment order is ready for fulfillment. | |
LineItems | String | Represents line items belonging to a fulfillment order. | |
RequestStatus | String | The status of the fulfillment. | |
Status | String | The status of the fulfillment order. | |
SupportedActions | String | The actions that can be performed on this fulfillment order. | |
MerchantRequests | String | A list of requests sent by the merchant to the fulfillment service for this fulfillment order.. | |
FulfillmentHolds | String | Represents the fulfillment holds applied on the fulfillment order. | |
InternationalDuties | String | The international duties relevant to the fulfillment order. | |
AssignedLocationAddress1 | String | The street address of the assigned location | |
AssignedLocationAddress2 | String | An optional additional field for the street address of the assigned location. | |
AssignedLocationCity | String | The city of the assigned location. | |
AssignedLocationCountryCode | String | The two-letter code for the country of the assigned location | |
AssignedLocationName | String | The name of the assigned location. | |
AssignedLocationPhone | String | The phone number of the assigned location. |
Retrieve information regarding store locations.
The Cloud uses the Shopify API to process search criteria that refer to the Id column. The supported SQL operator is '='. The Cloud processes other filters client-side within the Cloud. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.
For example, the following queries are processed server side:
SELECT * FROM Locations
SELECT * FROM Locations WHERE Id='123'
Name | Type | References | Description |
Id [KEY] | Long | The ID for the location. | |
Name | String | The name of the location. | |
Legacy | Boolean | Whether this is a fulfillment service location. If true, then the location is a fulfillment service location. If false, then the location was created by the merchant and isn't tied to a fulfillment service. | |
Address1 | String | The first line of the address. | |
Address2 | String | The second line of the address. | |
City | String | The city the location is in. | |
Zip | String | The zip or postal code. | |
Province | String | The province the location is in. | |
Country | String | The country the location is in. | |
Phone | String | The phone number of the location. This value can contain special characters like - and +. | |
CountryCode | String | The two-letter code (ISO 3166-1 alpha-2 format) corresponding to country the location is in. | |
CountryName | String | The name of the country the location is in. | |
CreatedAt | Datetime | The date and time when the location was created. | |
UpdatedAt | Datetime | The date and time when the location was last updated. | |
DeletedAt | Datetime | The date and time when the location was deleted. |
Query note attributes belonging to an order or draft order.
The Cloud uses the Shopify API to process search criteria that refer to the OrderId and OrderUpdatedAt columns. The supported SQL operators are '=' for OrderId and '>' and '<' for OrderUpdatedAt. The Cloud processes other filters client-side within the Cloud. For example, the following queries are processed server side.
SELECT * FROM NoteAttributes WHERE OrderId='123'
SELECT * FROM NoteAttributes WHERE OrderUpdatedAt > '2018-05-05'
Name | Type | References | Description |
OrderId | Long |
Orders.Id | The id of the order. |
OrderUpdatedAt | Datetime | The date and time when the order was last modified. | |
Name | String | The name of the note attribute. | |
Value | String | The value of the note attribute. |
Query note attributes belonging to an order or draft order.
The Cloud uses the Shopify API to process search criteria that refer to the OrderId and OrderUpdatedAt columns. The Cloud processes other filters client-side within the Cloud. For example, the following queries are processed server side.
SELECT * FROM OrderDiscountCodes WHERE OrderId = '1'
SELECT * FROM OrderDiscountCodes WHERE OrderId IN ('1', '2')
SELECT * FROM OrderDiscountCodes WHERE OrderUpdatedAt <= '2019-05-13 09:23:06.0'
SELECT * FROM OrderDiscountCodes WHERE OrderUpdatedAt >= '2019-05-13 09:23:06.0'
Name | Type | References | Description |
OrderId | Long |
Orders.Id | The id of the order. |
OrderUpdatedAt | Datetime | The date and time when the order was last modified. | |
Code | String | The name of the note attribute. | |
Amount | String | The value of the note attribute. | |
Type | String | The value of the note attribute. |
Query order item discount allocations.
Name | Type | References | Description |
ItemId | Long |
OrdersItems.ItemId | The id of the order item. Only available for orders. |
OrderId | Long |
Orders.Id | The id of the order. |
amount | String | The Amount of the item discount allocations. | |
ApplicationIndex | Int | The ApplicationIndex of the item discount allocations. | |
ShopMoneyAmount | Decimal | The ShopMoneyAmount of the item discount allocations. | |
ShopMoneyCurrencyCode | String | The ShopMoneyCurrencyCode of the item discount allocations. | |
PresentmentMoneyAmount | Decimal | The PresentmentMoneyAmount of the item discount allocations. | |
PresentmentMoneyCurrencyCode | String | The PresentmentMoneyCurrencyCode of the item discount allocations. | |
OrderUpdatedAt | Datetime | The date and time when the order was last modified. |
Query order item properties.
The Cloud uses the Shopify API to process search criteria that refer to the OrderId column. The supported SQL operators are '=' for OrderId. The Cloud processes other filters client-side within the Cloud. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.
For example, the following queries are processed server side. You can set the Type pseudo column to "DraftOrder" to list order items belonging to draft orders instead of orders, or you can set Type to "AbandonedCheckout" to list order items belonging to abandoned checkouts.
SELECT * FROM OrderItemProperties WHERE OrderId = '123'
Name | Type | References | Description |
ItemId | Long |
OrdersItems.ItemId | The id of the order item. Only available for orders. |
OrderId | Long |
Orders.Id | The id of the order. |
Name | String | The name of the item property. | |
Value | String | The value of the item property. |
Query order items.
Name | Type | References | Description |
ItemId | Long | The id of the order item. Only available for orders. | |
OrderId | Long |
Orders.Id | The id of the order. |
OrderUpdatedAt | Datetime | The date and time when the order was last modified. | |
ProductId | Long |
Products.Id | The id of the of the product beloning to the order item. |
ItemVariantId | Long | The id of the product variant. | |
ItemTitle | String | The title of the product. | |
Name | String | The name of the product variant. | |
ItemQuantity | Integer | The number of products that were purchased. | |
ItemPrice | Decimal | The price of the item before discounts have been applied. | |
ItemGrams | Integer | The weight of the item in grams. | |
SKU | String | A unique identifier of the item in the fulfillment. | |
VariantTitle | String | The title of the product variant. | |
Properties | String | Shows custom properties for this order item. | |
Vendor | String | The name of the supplier of the item. | |
FulFillmentsService | String | Service provider who is doing the fulfillment. | |
RequiresShipping | Boolean | States whether or not the fulfillment requires shipping. | |
Taxable | Boolean | States whether or not the product was taxable. | |
GiftCard | Boolean | States whether or not the order item is a gift card. | |
FulfillableQuantity | Integer | The amount available to fulfill. | |
TotalDiscount | Decimal | The total discount amount applied to this order item. | |
FulfillmentStatus | String | How far along an order is in terms order items fulfilled. | |
AppliedDiscountTitle | String | The title of the applied discount for this order. Only available for draft orders. | |
AppliedDiscountDescription | String | The description of the applied discount for order. Only available for draft orders. | |
AppliedDiscountValue | Decimal | The value of the applied discount for this order. Only available for draft orders. | |
AppliedDiscountValueType | String | The value type of the applied discount for order. Only available for draft orders. | |
AppliedDiscountAmount | Decimal | The amount of the applied discount for this order. Only available for draft orders. |
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
Name | Type | Description | |
Type | String | The type of order item, can be one of the following(Order, DraftOrder, AbandonedCheckout).
The allowed values are Order, DraftOrder, AbandonedCheckout. The default value is Order. | |
Status | String | Filter orders by their status. Valid values are: open: show only open orders, closed: show only closed orders, cancelled: show only canceled orders, any: show orders of any status,default: open. |
Retrieves a list of all payouts ordered by payout date, with the most recent being first.
The Cloud uses the Shopify API to process search criteria that refer to the Id, Status and Date columns. The supported SQL operator is '=' for Id, Status and Date columns. The Cloud processes other filters client-side within the Cloud. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.
For example, the following queries are processed server side:
SELECT * FROM Payouts
SELECT * FROM Payouts WHERE Id='123'
SELECT * FROM Payouts WHERE Status='pending'
SELECT * FROM Payouts WHERE Date = '2018-10-30'
Name | Type | References | Description |
Id [KEY] | Long | The unique identifier of the payout. | |
Status | String | The transfer status of the payout. | |
Currency | String | The ISO 4217 currency code of the payout. | |
Amount | Decimal | The total amount of the payout, in a decimal formatted string. | |
Date | Datetime | The date the payout was issued. |
Retrieves a list of all balance transactions ordered by processing time, with the most recent being first.
Name | Type | References | Description |
Id [KEY] | Long | The unique identifier of the transaction. | |
Type | String | The type of the balance transaction. | |
Test | Boolean | If the transaction was created for a test mode Order or payment. | |
PayoutId | Long |
Payouts.Id | The id of the payout the transaction was paid out in. |
PayoutStatus | String | The status of the payout the transaction was paid out in, or pending if the transaction has not yet been included in a payout. | |
Currency | String | The ISO 4217 currency code of the transaction. | |
Amount | Decimal | The gross amount of the transaction, in a decimal formatted string. | |
Fee | Decimal | The total amount of fees deducted from the transaction amount. | |
Net | Decimal | The net amount of the transaction. | |
SourceId | Long | The id of the resource leading to the transaction. | |
SourceType | String | The type of the resource leading to the transaction. | |
SourceOrderTransactionId | String | The id of the Order Transaction that resulted in this balance transaction. | |
SourceOrderId | String | The id of the Order that this transaction ultimately originated from. | |
ProcessedAt | Datetime | The time the transaction was processed. |
Query product options.
The Cloud uses the Shopify API to process search criteria that refer to the ProductId and ProductUpdatedAt columns. The supported SQL operators are '=' for ProductId and '>' and '<' for ProductUpdatedAt. The Cloud processes other filters client-side within the Cloud. For example, the following queries are processed server side.
SELECT * FROM ProductOptions
SELECT * FROM ProductOptions WHERE ProductId='123'
SELECT * FROM ProductOptions WHERE ProductUpdatedAt > '2018-05-10'
You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.
Name | Type | References | Description |
Id [KEY] | Long | The id of the product option. | |
ProductId | Long |
Products.Id | The id of the product. |
ProductUpdatedAt | Datetime | The date and time when the product was last modified. | |
Name | String | The name of the product option. | |
Position | Integer | The position of the product option. | |
Values | String | The values of the product option. |
Create and query transactions.
The Cloud uses the Shopify API to process search criteria that refer to the OrderId and RefundId column. The Cloud processes other filters client-side within the Cloud.
The Shopify APIs requires the search criteria to contain OrderId. For example, the following query is processed server side.
SELECT * FROM RefundAdjustments WHERE OrderId = '123'
When performing a query without a filter or a filter other then the OrderId column, the Cloud will retrieve the entire list of refunds and perform the filters client-side.
The Shopify API requires that an OrderId be specified when retrieving refunds. Therefore to retrieve all the refunds, the Cloud will first retrieve all the OrderIds
from the Orders table (essentially performing the query: SELECT Id FROM Orders). Once the OrderId values have been retrieved, the Cloud will issue individual requests to the
Shopify API to retrieve all the refunds for each OrderId to build the result set.
SELECT * FROM RefundAdjustments
SELECT * FROM RefundAdjustments WHERE OrderId = '123' AND RefundId = '123'
You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false. Other search criteria will be ignored and an unfiltered response will be returned.
Name | Type | References | Description |
Id [KEY] | Long | A unique numeric identifier for the refund adjustment. | |
OrderId | Long |
Orders.Id | The unique identifier for the order that the order adjustment is associated with. |
RefundId | Long |
Refunds.Id | The unique identifier for the refund that the order adjustment is associated with. |
Amount | Decimal | The value of the discrepancy between the calculated refund and the actual refund. | |
TaxAmount | Decimal | The taxes that are added to amount, such as applicable shipping taxes added to a shipping refund. | |
Kind | String | The order adjustment type. | |
Reason | String | The reason for the order adjustment. | |
AmountShopMoney | Decimal | The amount of the order adjustment in shop. | |
AmountShopCurrency | String | The currency of the order adjustment in shop. | |
AmountPresentmentMoney | Decimal | The amount of the order adjustment in presentment. | |
AmountPresentmentCurrency | String | The currency of the order adjustment in presentment. | |
TaxAmountShopMoney | Decimal | The tax amount of the order adjustment in shop. | |
TaxAmountShopCurrency | String | The tax currency of the order adjustment in shop. | |
TaxAmountPresentmentMoney | Decimal | The tax amount of the order adjustment in presentment. | |
TaxAmountPresentmentCurrency | String | The tax currency of the order adjustment in presentment. |
Get data on OrdersItems that have been refunded.
Name | Type | References | Description |
ItemId [KEY] | Long |
OrdersItems.ItemId | The id of the order item. Only available for orders. |
OrderID [KEY] | Long |
Refunds.Id | The id of the refund this refunded item belongs to. |
RefundId [KEY] | Long |
Refunds.Id | The id of the refund this refunded item belongs to. |
LocationId | Long | The unique identifier of the location tied to the refund item | |
ProductId | Long |
Products.Id | The id of the of the product beloning to the order item. |
ItemVariantId | Long | The id of the product variant. | |
ItemTitle | String | The title of the product. | |
Name | String | The name of the product variant. | |
ItemQuantity | Integer | The number of products that were purchased. | |
ItemPrice | Decimal | The price of the item before discounts have been applied. | |
ItemGrams | Integer | The weight of the item in grams. | |
SKU | String | A unique identifier of the item in the fulfillment. | |
VariantTitle | String | The title of the product variant. | |
Properties | String | Shows custom properties for this order item. | |
Vendor | String | The name of the supplier of the item. | |
FulFillmentsService | String | Service provider who is doing the fulfillment. | |
RequiresShipping | Boolean | States whether or not the fulfillment requires shipping. | |
Taxable | Boolean | States whether or not the product was taxable. | |
GiftCard | Boolean | States whether or not the order item is a gift card. | |
FulfillableQuantity | Integer | The amount available to fulfill. | |
TotalDiscount | Decimal | The total discount amount applied to this order item. | |
FulfillmentStatus | String | How far along an order is in terms order items fulfilled. | |
RefundSubtotal | Decimal | The refunded amount for this item. This is calculated by multiplying ItemPrice with RefundQuantity. | |
RefundQuantity | Integer | The quantity of the item refunded. | |
RefundRestockType | String | The type of the restock action. | |
RefundSubtotalPresentmentAmount | Decimal | The total amount of the presentment money. | |
RefundSubtotalPresentmentCurrencyCode | String | The currency code of the presentment money. | |
RefundSubtotalShopAmount | Decimal | The total amount of the shop money. | |
RefundSubtotalShopCurrencyCode | String | The currency code of the shop money. | |
RefundTotalTax | Decimal | Total tax for the refunded item. | |
RefundTotalTaxPresentmentAmount | Decimal | Total tax amount for the presentment money. | |
RefundTotalTaxPresentmentCurrencyCode | String | Currency code for the tax on presentment money. | |
RefundTotalTaxShopAmount | Decimal | Total tax amount for the shop money. | |
RefundTotalTaxShopCurrencyCode | String | Currency code for the tax on shop money. | |
AppliedDiscountTitle | String | The title of the applied discount for this order. Only available for draft orders. | |
AppliedDiscountDescription | String | The description of the applied discount for order. Only available for draft orders. | |
AppliedDiscountValue | Decimal | The value of the applied discount for this order. Only available for draft orders. | |
AppliedDiscountValueType | String | The value type of the applied discount for order. Only available for draft orders. | |
AppliedDiscountAmount | Decimal | The amount of the applied discount for this order. Only available for draft orders. |
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
Name | Type | Description | |
Status | String | Filter orders by their status. Valid values are: open: show only open orders, closed: show only closed orders, cancelled: show only canceled orders, any: show orders of any status,default: open. |
Query transactions for Refund Object.
The Cloud uses the Shopify API to process search criteria that refer to the OrderId and RefundId column. The Cloud processes other filters client-side within the Cloud.
The Shopify APIs requires the search criteria to contain OrderId. For example, the following query is processed server side.
SELECT * FROM RefundTransactions WHERE OrderId = '123'
When performing a query without a filter or a filter other then the OrderId column, the Cloud will retrieve the entire list of refunds and perform the filters client-side.
The Shopify API requires that an OrderId be specified when retrieving refunds. Therefore to retrieve all the refunds, the Cloud will first retrieve all the OrderIds
from the Orders table (essentially performing the query: SELECT Id FROM Orders). Once the OrderId values have been retrieved, the Cloud will issue individual requests to the
Shopify API to retrieve all the refunds for each OrderId to build the result set.
SELECT * FROM RefundTransactions
SELECT * FROM RefundTransactions WHERE OrderId = '123' AND RefundId = '123'
You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false. Other search criteria will be ignored and an unfiltered response will be returned.
Name | Type | References | Description |
TransactionsId [KEY] | Long | The ID for the refund transaction. | |
OrderId | Long |
Orders.Id | The ID for the order that the transaction is associated with. |
RefundId | Long |
Refunds.Id | The unique identifier for the refund associated with. |
Amount | Decimal | The amount of money included in the transaction. | |
Authorization | String | The authorization code associated with the transaction.. | |
CreatedAt | Datetime | The date and time (ISO 8601 format) when the transaction was created. | |
Currency | String | The three-letter code (ISO 4217 format) for the currency used for the payment. | |
DeviceId | String | The ID for the device. | |
ErrorCode | String | A standardized error code, independent of the payment provider. | |
GraphqlAPIId | String | The order adjustment type. | |
Gateway | String | The name of the gateway the transaction was issued through. | |
Kind | String | The transaction's type. | |
LocationId | String | The ID of the physical location where the transaction was processed. | |
Message | String | A string generated by the payment provider with additional information about why the transaction succeeded or failed. | |
ParentId | Long | The ID of an associated transaction.. | |
ProcessedAt | Datetime | The date and time (ISO 8601 format) when a transaction was processed. | |
Status | String | The status of the transaction. | |
SourceName | String | The origin of the transaction. | |
Test | Boolean | Whether the transaction is a test transaction. | |
UserId | String | The ID for the user who was logged into the Shopify POS device when the order was processed, if applicable. |
To query all the Reports.
The Cloud uses the Shopify API to process search criteria that refer to the Id and UpdatedAt columns. The Cloud processes other filters client-side within the Cloud. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria that refers to other columns will cause an error.
For example, the following queries are processed server side.
SELECT * FROM Reports
SELECT * FROM Reports WHERE Id = '123'
SELECT * FROM Reports WHERE UpdatedAt > '2017-10-25'
Name | Type | References | Description |
Id [KEY] | Long | The unique numeric identifier for the report. | |
Name | String | The name of the report. | |
ShopifyQl | String | The ShopifyQL query that generates the report. | |
UpdatedAt | Datetime | The date and time (ISO 8601) when the report was last modified. | |
Category | String | The category for the report. When you create a report, the API will return custom_app_reports. |
Query Shipping item discount allocations.
Name | Type | References | Description |
ItemId | Long |
ShippingItems.ItemId | The id of the shipping item. Only available for orders. |
OrderId | Long |
Orders.Id | The id of the order. |
Amount | String | The Amount of the item discount allocations. | |
ShopMoneyAmount | Decimal | The ShopMoneyAmount of the item discount allocations. | |
ShopMoneyCurrencyCode | String | The ShopMoneyCurrencyCode of the item discount allocations. | |
PresentmentMoneyAmount | Decimal | The PresentmentMoneyAmount of the item discount allocations. | |
PresentmentMoneyCurrencyCode | String | The PresentmentMoneyCurrencyCode of the item discount allocations. | |
DiscountApplicationIndex | Integer | The Discount application index for an order. | |
OrderUpdatedAt | Datetime | The date and time when the order was last modified. |
Query order shipping.
The Cloud uses the Shopify API to process search criteria that refer to the OrderId and OrderUpdatedAt columns. The supported SQL operators are '=' for OrderId and '>' and '<' for OrderUpdatedAt. The Cloud processes other filters client-side within the Cloud. For example, the following queries are processed server side.
SELECT * FROM ShippingItems
SELECT * FROM ShippingItems WHERE OrderId='123'
SELECT * FROM ShippingItems WHERE OrderUpdatedAt > '2018-05-05'
You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.
Name | Type | References | Description |
Id [KEY] | Long | The id of the shipping item. | |
OrderId [KEY] | Long |
Orders.Id | The id of the order. |
OrderUpdatedAt | Datetime | The date and time when the order was last modified. | |
Price | Decimal | The price of this shipping method. | |
Code | String | A reference to the shipping method. | |
Title | String | The title of the shipping method. | |
Source | String | The source of the shipping method. | |
CarrierIdentifier | String | A reference to the carrier service that provided the rate. | |
RequestedFulfillmentServiceId | String | A reference to the fulfillment service that is being requested for the shipping method. |
Retrieve information regarding shipping zones.
The Cloud processes all filters client-side within the Cloud. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.
The following query is the only one processed server side:
SELECT * FROM ShippingZones
Name | Type | References | Description |
Id [KEY] | Long | The unique numeric identifier for the shipping zone. | |
Name | String | The name of the shipping zone, specified by the user. | |
ProfileId | String | The ID of the shipping zone's delivery profile. Shipping profiles allow merchants to create product-based or location-based shipping rates. | |
LocationGroupId | String | The ID of the shipping zone's location group. Location groups allow merchants to create shipping rates that apply only to the specific locations in the group. | |
Countries | String | A list of countries that belong to the shipping zone. | |
CarrierShippingRateProviders | String | Information about carrier shipping providers and the rates used. | |
PriceBasedShippingRates | String | Information about price based shipping rates used. | |
WeightBasedShippingRates | String | Information about weight based shipping rates used. |
Contains general settings and information about the shop.
The Cloud processes the WHERE clause client-side within the Cloud for all queries to this table.
You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.
For example, the following query is the only query processed server side:
SELECT * FROM Shop
Name | Type | References | Description |
Id | Long | A unique numeric identifier for the shop. | |
Name | String | The name of the shop. | |
String | The contact email address for the shop. | ||
Address1 | String | The shop's street address. | |
Address2 | String | The shop's additional street address (apt, suite, etc.). | |
City | String | The city in which the shop is located. | |
Country | String | The shop's country (by default equal to the two-letter country code). | |
CountryCode | String | The two-letter country code corresponding to the shop's country. | |
CountryName | String | The shop's normalized country name. | |
CustomerEmail | String | The customer's email. | |
Currency | String | The three-letter code for the currency that the shop accepts. | |
Domain | String | The shop's domain. | |
GoogleAppsDomain | String | The URL of the domain if the shop has a google apps domain. | |
GoogleAppsLoginEnabled | String | Indicated whether the shop has google apps login enabled. | |
Latitude | Double | Geographic coordinate specifying the north/south location of a shop. | |
Longitude | Double | Geographic coordinate specifying the east/west location of a shop. | |
MoneyFormat | String | A string representing the way currency is formatted when the currency isn't specified. | |
MoneyWithCurrencyFormat | String | A string representing the way currency is formatted when the currency is specified. | |
WeightUnit | String | A string representing the default unit of weight measurement for the shop. | |
MyshopifyDomain | String | The shop's 'myshopify.com' domain. | |
PlanName | String | The name of the Shopify plan the shop is on. | |
HasDiscounts | Boolean | Indicates if any active discounts exist for the shop. | |
HasGiftCards | Boolean | Indicates if any active gift cards exist for the shop. | |
PlanDisplayName | String | The display name of the Shopify plan the shop is on. | |
PasswordEnabled | Boolean | Indicates whether the Storefront password protection is enabled. | |
Phone | String | The contact phone number for the shop. | |
PrimaryLocale | String | The shop's primary locale. | |
Province | String | The shop's normalized province or state name. | |
ProvinceCode | String | The two-letter code for the shop's province or state. | |
ShopOwner | String | The username of the shop owner. | |
Source | String | ||
ForceSSL | Boolean | Indicates whether the shop forces requests made to its resources to be made over SSL. | |
TaxShipping | Boolean | Specifies whether or not taxes were charged for shipping. | |
TaxesIncluding | Boolean | The setting for whether applicable taxes are included in product prices. | |
CountryTaxes | Boolean | The setting for whether the shop is applying taxes on a per-county basis or not (US-only). | |
Timezone | String | The name of the timezone the shop is in. | |
IANATimezone | String | The named timezone assigned by the IANA. | |
Zip | String | The zip or postal code of the shop's address. | |
HasStorefront | Boolean | Indicates whether the shop has web-based storefront or not. | |
CreatedAt | Datetime | The date and time when the shop was created. | |
UpdatedAt | Datetime | The date and time when the shop was last updated. | |
SetupRequired | Boolean | Indicates whether the shop has any outstanding setup steps or not. |
Query order taxes.
The Cloud uses the Shopify API to process search criteria that refer to the OrderId and OrderUpdatedAt columns. The supported SQL operators are '=' for OrderId and '>' and '<' for OrderUpdatedAt. The Cloud processes other filters client-side within the Cloud. For example, the following queries are processed server side.
SELECT * FROM TaxItems
SELECT * FROM TaxItems WHERE OrderId = '123'
SELECT * FROM TaxItems WHERE OrderUpdatedAt > '2018-05-21'
You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.
Name | Type | References | Description |
OrderId | Long |
Orders.Id | The id of the order. |
OrderUpdatedAt | Datetime | The date and time when the order was last modified. | |
ChannelLiable | Boolean | Whether the channel that submitted the tax line is liable for remitting. | |
TaxItemPrice | Decimal | The amount of tax to be charged. | |
TaxItemRate | Decimal | The rate of tax to be applied. | |
TaxItemTitle | String | The name of the tax. |
Stored procedures are function-like interfaces that extend the functionality of the Cloud beyond simple SELECT/INSERT/UPDATE/DELETE operations with Shopify.
Stored procedures accept a list of parameters, perform their intended function, and then return, if applicable, any relevant response data from Shopify, along with an indication of whether the procedure succeeded or failed.
Name | Description |
ApproveComment | Approves a comment. |
AssignDefaultAddress | Assign a default address to a customer. |
ChangeSpamStatus | Changes spam status of a comment. |
CompleteDraftOrder | Complete a draft order. |
CreateAccountActivationUrl | Creates an account activation URL for the specified customer. |
ListProduct | Create a product listing to publish a product to your app. |
RestoreComment | Restores a previously removed comment. |
SendInvite | Send a default or customized invite to a customer. |
SendInvoice | Send a default or customized invoice to a customer. |
Approves a comment.
Name | Type | Description |
Id | String | The id of the comment that needs to be approved. |
Name | Type | Description |
Success | String | Whether the operation completed successfully or not. |
Assign a default address to a customer.
Name | Type | Description |
CustomerId | String | The id of the customer. |
CustomerAddressId | String | The id of the address you are setting as default. This address must belong to the customer specified in CustomerId. You cannot use the address of a different customer. |
Name | Type | Description |
Success | String | Whether the operation completed successfully or not. |
Changes spam status of a comment.
Name | Type | Description |
Id | String | The id of the comment whose status needs to be changed |
Spam | String | Value of spam status
The allowed values are yes, no. |
Name | Type | Description |
Success | String | Whether the operation completed successfully or not. |
Complete a draft order.
Name | Type | Description |
Id | String | The id of the draft order. |
PaymentPending | String | Sets payment pending to true.
The default value is false. |
Name | Type | Description |
Success | String | Whether the operation completed successfully or not. |
Creates an account activation URL for the specified customer.
Name | Type | Description |
CustomerId | String | The id of the customer. |
Name | Type | Description |
AccountActivationUrl | String | The URL the customer needs to access to activate his account. |
Gets an authentication token from Shopify.
Name | Type | Description |
AuthMode | String | The type of authentication mode to use. Select App for getting authentication tokens via a desktop app. Select Web for getting authentication tokens via a Web app.
The allowed values are APP, WEB. The default value is APP. |
CallbackUrl | String | The URL the user will be redirected to after authorizing your application. This value must match the Redirect URL you have specified in the Shopify app settings. Only needed when the Authmode parameter is Web. |
Verifier | String | The verifier returned from Shopify after the user has authorized your app to have access to their data. This value will be returned as a parameter to the callback URL. |
State | String | Encodes state of the app, which will be returned verbatim in the response and can be used to match the response up to a given request. |
Scope | String | The scope or permissions you are requesting.
The default value is read_products,write_products,read_product_listings,read_customers,write_customers,read_orders,write_orders,read_draft_orders,write_draft_orders,read_inventory,write_inventory,read_locations,read_fulfillments,write_fulfillments,read_assigned_fulfillment_orders,read_merchant_managed_fulfillment_orders,read_third_party_fulfillment_orders,read_shipping,write_shipping,read_price_rules,write_price_rules,read_discounts,write_discounts,read_marketing_events,write_marketing_events,read_shopify_payments_payouts. |
Name | Type | Description |
OAuthAccessToken | String | The access token used for communication with Shopify. |
ExpiresIn | String | The remaining lifetime on the access token. A -1 denotes that it will not expire. |
Gets the authorization URL that must be opened separately by the user to grant access to your application. Only needed when developing Web apps. You will request the OAuthAccessToken from this URL.
Name | Type | Description |
CallbackUrl | String | The URL the user will be redirected to after authorizing your application. This value must match the Redirect URL in the Shopify app settings. |
State | String | Encodes state of the app, which will be returned verbatim in the response and can be used to match the response up to a given request. |
Scope | String | The scope or permissions you are requesting.
The default value is read_products,write_products,read_product_listings,read_customers,write_customers,read_orders,write_orders,read_draft_orders,write_draft_orders,read_inventory,write_inventory,read_locations,read_fulfillments,write_fulfillments,read_assigned_fulfillment_orders,read_merchant_managed_fulfillment_orders,read_third_party_fulfillment_orders,read_shipping,write_shipping,read_price_rules,write_price_rules,read_discounts,write_discounts,read_marketing_events,write_marketing_events,read_shopify_payments_payouts. |
Name | Type | Description |
URL | String | The authorization URL, entered into a Web browser to obtain the verifier token and authorize your app. |
Create a product listing to publish a product to your app.
Name | Type | Description |
ProductId | String | The id of the product to be added in product listings. |
Name | Type | Description |
Success | String | Whether the operation completed successfully or not. |
Restores a previously removed comment.
Name | Type | Description |
Id | String | The id of the comment that needs to be restored. |
Name | Type | Description |
Success | String | Whether the operation completed successfully or not. |
Send a default or customized invite to a customer.
Name | Type | Description |
Id | String | The customer id to whom the invite will be sent. |
To | String | The email address of whom to send the invite. |
From | String | The email address of the person who sent the invite. |
BCC | String | A comma separated list of email addresses to whom a blind carbon copy of the email will be sent. |
Subject | String | The subject of the email. |
CustomMessage | String | A custom message to be sent. |
Name | Type | Description |
Success | String | Whether the operation completed successfully or not. |
Send a default or customized invoice to a customer.
Name | Type | Description |
Id | String | The id of the draft order invoice which will be sent. |
To | String | The email address of whom to send the invoice. |
From | String | The email address of the person who sent the invoice. |
BCC | String | A comma separated list of email addresses to whom a blind carbon copy of the email will be sent. |
Subject | String | The subject of the email. |
CustomMessage | String | A custom message to be sent. |
Name | Type | Description |
Success | String | Whether the operation completed successfully or not. |
The CData Cloud models the Shopify API as relational views, and stored procedures.
To use GraphQL Data Model, simply set Schema to GraphQL.
Views are tables that cannot be modified, such as Events, Patouts, Reports. Typically, model data that is read-only and cannot be updated are shown as views.
Stored Procedures are function-like interfaces to the data source. They can be used to search, update, and modify information in the data source.
When UseBulkApi is set to True CData Cloud performs Shopify Bulk Operations. This option offers better performance when you need to select or export a lot of data from Shopify.
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 |
Customers | List of customers. |
DraftOrders | List of saved draft orders. |
FulfillmentEvents | Retrieves the history of events associated with one or many fulfillments. |
FulfillmentOrders | FulfillmentOrders represents a group of one or more items in an order that are to be fulfilled from the same location. |
Fulfillments | Fulfillment represents a shipment of one or more items in an order. |
FulfillmentServices | Lists fulfillmentServices which Represents service that prepares and ships orders on behalf of the store owner. |
Metafields | Retrieves a list of metafields that belong to one or many resource IDs. |
OrderRisks | The order risks associated with this order. |
Orders | Tax rates with their effective dates of application |
OrderTransactions | List of all transactions associated with the orders. |
ProductImages | The images associated with the product. |
ProductOptions | A list of product options. The limit is specified by Shop.resourceLimits.maxProductOptions. |
Products | List of products. |
ProductVariants | List of the product variants. |
Refunds | Represents a refund of items or transactions in an order. |
List of customers.
The Cloud uses the Shopify API to process WHERE clause conditions built with the following column and operator. The Cloud processes other filters client-side within the Cloud.
SELECT * FROM Customers
SELECT * FROM Customers WHERE Id = '123'
SELECT * FROM Customers WHERE UpdatedAt = '2017-10-25'
SELECT * FROM Customers WHERE UpdatedAt <= '2017-10-25'
SELECT * FROM Customers WHERE UpdatedAt >= '2017-10-25'
SELECT * FROM Customers WHERE Email = 'abcxyz@gmail.com'
SELECT * FROM Customers WHERE OrdersCount = 5
SELECT * FROM Customers WHERE Phone = '999999999'
SELECT * FROM Customers WHERE State = 'State1'
You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.
Name | Type | References | Description |
Id [KEY] | String | Globally unique identifier. | |
FirstName | String | The customer's first name. | |
LastName | String | The customer's last name. | |
String | The customer's email address. | ||
DisplayName | String | The full name of the customer, based on the values for first_name and last_name. If the first_name and last_name are not available, then this falls back to the customer's email address, and if that is not available, the customer's phone number. | |
CreatedAt | Datetime | The date and time when the customer was added to the store. | |
UpdatedAt | Datetime | The date and time when the customer was last updated. | |
LastOrderId | String |
Orders.Id | The Id of the customer's last order. |
AcceptsMarketing | Boolean | Whether the customer has agreed to receive marketing materials. | |
AcceptsMarketingUpdatedAt | Datetime | The date and time when the customer consented or objected to receiving marketing material by email. | |
AverageOrderAmount | Decimal | The average amount that the customer spent per order. | |
AverageOrderAmountCurrency | String | The currency of the average amount that the customer spent per order. | |
CanDelete | Boolean | Whether the merchant can delete the customer from their store. A customer can be deleted from a store only if they have not yet made an order. After a customer makes an order, they can't be deleted from a store. | |
DefaultAddress1 | String | The first line of the address. Typically the street address or PO Box number. | |
DefaultAddress2 | String | The second line of the address. Typically the number of the apartment, suite, or unit. | |
DefaultAddressCity | String | The name of the city, district, village, or town. | |
DefaultAddressCompany | String | The name of the customer's company or organization. | |
DefaultAddressCountry | String | The name of the country. | |
DefaultAddressCountryCode | String | The two-letter code for the country of the address. For example, US. | |
DefaultAddressFirstName | String | The first name of the customer. | |
DefaultAddressFormattedArea | String | A comma-separated list of the values for city, province, and country. | |
DefaultAddressId | String | Globally unique identifier. | |
DefaultAddressLastName | String | The last name of the customer. | |
DefaultAddressLatitude | Double | The latitude coordinate of the customer address. | |
DefaultAddressLongitude | Double | The longitude coordinate of the customer address. | |
DefaultAddressName | String | The full name of the customer, based on firstName and lastName. | |
DefaultAddressPhone | String | A unique phone number for the customer. Formatted using E.164 standard. For example, +16135551111. | |
DefaultAddressProvince | String | The region of the address, such as the province, state, or district. | |
DefaultAddressProvinceCode | String | The two-letter code for the region. For example, ON. | |
DefaultAddressZip | String | The zip or postal code of the address. | |
HasNote | Boolean | Whether the customer has a note associated with them. | |
HasTimelineComment | Boolean | Whether the merchant has added timeline comments about the customer on the customer's page. | |
LegacyResourceId | Long | The ID of the corresponding resource in the REST Admin API. | |
LifetimeDuration | String | The amount of time since the customer was first added to the store. Example: 'about 12 years'. | |
Locale | String | The customer's locale. | |
MarketingOptInLevel | String | The marketing subscription opt-in level (as described by the M3AAWG best practices guideline) that the customer gave when they consented to receive marketing material by email. If the customer does not accept email marketing, then this property will be null. | |
Note | String | A note about the customer. | |
OrdersCount | Long | The number of orders that the customer has made at the store in their lifetime. | |
Phone | String | The customer's phone number. | |
State | String | The state of the customer's account with the shop.
The allowed values are DECLINED, DISABLED, ENABLED, INVITED. | |
TaxExempt | Boolean | Whether the customer is exempt from being charged taxes on their orders. | |
TotalSpentAmount | Decimal | The total amount that the customer has spent on orders in their lifetime. | |
TotalSpentCurrency | String | The currency of the total amount that the customer has spent on orders in their lifetime. | |
ValidEmailAddress | Boolean | Whether the email address is formatted correctly. This does not guarantee that the email address actually exists. | |
VerifiedEmail | Boolean | Whether the customer has verified their email address. |
List of saved draft orders.
The Cloud uses the Shopify API to process WHERE clause conditions built with the following column and operator. The Cloud processes other filters client-side within the Cloud.
For example, the following queries are processed server side:
SELECT * FROM DraftOrders
SELECT * FROM DraftOrders WHERE Id='123'
SELECT * FROM DraftOrders WHERE Status='completed'
SELECT * FROM DraftOrders WHERE CreatedAt > '2018-02-05'
SELECT * FROM DraftOrders WHERE UpdatedAt > '2018-02-05'
Name | Type | References | Description |
Id [KEY] | String | Globally unique identifier. | |
Name | String | Unique identifier for the draft order, which is unique within the store. For example, #D1223. | |
CreatedAt | Datetime | Date and time when the draft order was created in Shopify. | |
UpdatedAt | Datetime | Date and time when the draft order was last changed. The format is YYYY-MM-DD HH:mm:ss (for example, 2016-02-05 17:04:01). | |
CompletedAt | Datetime | Date and time when the draft order converted to a new order, and the draft order's status changed to Completed. | |
AppliedDiscountAmount | Decimal | The total amount that the customer has spent on orders in their lifetime. | |
AppliedDiscountCurrency | String | The currency of the total amount that the customer has spent on orders in their lifetime. | |
AppliedDiscountDescription | String | Description of the order-level discount. | |
AppliedDiscountTitle | String | Name of the order-level discount. | |
AppliedDiscountValue | Float | Amount of the order level discount (when value_type is percentage, the value in this field is the percentage discount). | |
AppliedDiscountValueType | String | Type of the order-level discount. | |
BillingAddress1 | String | The first line of the address. Typically the street address or PO Box number. | |
BillingAddress2 | String | The second line of the address. Typically the number of the apartment, suite, or unit. | |
BillingAddressCity | String | The name of the city, district, village, or town. | |
BillingAddressCompany | String | The name of the customer's company or organization. | |
BillingAddressCountry | String | The name of the country. | |
BillingAddressCountryCodeV2 | String | The two-letter code for the country of the address. For example, US. | |
BillingAddressFirstName | String | The first name of the customer. | |
BillingAddressFormattedArea | String | A comma-separated list of the values for city, province, and country. | |
BillingAddressId | String | Globally unique identifier. | |
BillingAddressLastName | String | The last name of the customer. | |
BillingAddressLatitude | Double | The latitude coordinate of the customer address. | |
BillingAddressLongitude | Double | The longitude coordinate of the customer address. | |
BillingAddressName | String | The full name of the customer, based on firstName and lastName. | |
BillingAddressPhone | String | A unique phone number for the customer. Formatted using E.164 standard. For example, +16135551111. | |
BillingAddressProvince | String | The region of the address, such as the province, state, or district. | |
BillingAddressProvinceCode | String | The two-letter code for the region. For example, ON. | |
BillingAddressZip | String | The zip or postal code of the address. | |
CurrencyCode | String | Three letter code for the currency of the store at the time that the invoice is sent. | |
CustomerId | String |
Customers.Id | Customer who will be sent an invoice for the draft order, if there is one. |
String | Email address of the customer, which is used to send notifications to. | ||
HasTimelineComment | Boolean | Whether the merchant has added timeline comments to the draft order. | |
InvoiceSentAt | Datetime | Date and time when the invoice was last emailed to the customer. | |
InvoiceUrl | String | Link to the checkout, which is sent to your customer in the invoice email. | |
LegacyResourceId | Long | The ID of the corresponding resource in the REST Admin API. | |
Note2 | String | Text from an optional note attached to the draft order. | |
OrderId | String |
Orders.Id | Order that was created from this draft order. |
ShippingAddress1 | String | The first line of the address. Typically the street address or PO Box number. | |
ShippingAddress2 | String | The second line of the address. Typically the number of the apartment, suite, or unit. | |
ShippingAddressCity | String | The name of the city, district, village, or town. | |
ShippingAddressCompany | String | The name of the customer's company or organization. | |
ShippingAddressCountry | String | The name of the country. | |
ShippingAddressCountryCodeV2 | String | The two-letter code for the country of the address. For example, US. | |
ShippingAddressFirstName | String | The first name of the customer. | |
ShippingAddressFormattedArea | String | A comma-separated list of the values for city, province, and country. | |
ShippingAddressId | String | Globally unique identifier. | |
ShippingAddressLastName | String | The last name of the customer. | |
ShippingAddressLatitude | Double | The latitude coordinate of the customer address. | |
ShippingAddressLongitude | Double | The longitude coordinate of the customer address. | |
ShippingAddressName | String | The full name of the customer, based on firstName and lastName. | |
ShippingAddressPhone | String | A unique phone number for the customer. Formatted using E.164 standard. For example, +16135551111. | |
ShippingAddressProvince | String | The region of the address, such as the province, state, or district. | |
ShippingAddressProvinceCode | String | The two-letter code for the region. For example, ON. | |
ShippingAddressZip | String | The zip or postal code of the address. | |
Status | String | Status of the draft order. | |
SubtotalPrice | Decimal | Subtotal of the line items and their discounts (does not contain shipping charges or shipping discounts, or taxes). | |
TaxExempt | Boolean | Whether the draft order is tax exempt. | |
TaxesIncluded | Boolean | Whether the line item prices include taxes. | |
TotalPrice | Decimal | Total amount of the draft order (includes taxes, shipping charges, and discounts). | |
TotalShippingPrice | Decimal | Total shipping charge for the draft order. | |
TotalTax | Decimal | Total amount of taxes for the draft order. | |
TotalWeight | Long | Total weight (grams) of the draft order. |
Retrieves the history of events associated with one or many fulfillments.
Name | Type | References | Description |
Id [KEY] | String | A globally-unique identifier. | |
OrderId | String |
Orders.Id | A globally-unique identifier. |
FulfillmentId | String |
Fulfillments.Id | A globally-unique identifier. |
Status | String | The status of this fulfillment event.
The allowed values are LABEL_PURCHASED, LABEL_PRINTED, READY_FOR_PICKUP, CONFIRMED, IN_TRANSIT, OUT_FOR_DELIVERY, ATTEMPTED_DELIVERY, DELIVERED, FAILURE. | |
HappenedAt | Datetime | The time at which this fulfillment event happened. |
FulfillmentOrders represents a group of one or more items in an order that are to be fulfilled from the same location.
The Cloud processes filters client-side within the Cloud.
Name | Type | References | Description |
Id [KEY] | String | Globally unique identifier. | |
AssignedLocationAddress1 | String | The first line of the address for the location. | |
AssignedLocationAddress2 | String | The second line of the address for the location. | |
AssignedLocationCity | String | The city of the location. | |
AssignedLocationCountryCode | String | The two-letter country code of the location. | |
AssignedLocationName | String | The name of the location. | |
AssignedLocationPhone | String | The phone number of the location. | |
AssignedLocationProvince | String | The province of the location. | |
AssignedLocationLocationActivatable | Boolean | Whether this location can be reactivated. | |
AssignedLocationLocationAddressVerified | Boolean | Whether the location address has been verified. | |
AssignedLocationLocationDeactivatable | Boolean | Whether this location can be deactivated. | |
AssignedLocationLocationDeactivatedAt | String | Date and time the location was deactivated (null if location is still active). | |
AssignedLocationLocationFulfillsOnlineOrder | String | Indicates whether this location can fulfill online orders. | |
AssignedLocationLocationHasActiveInventory | Boolean | Indicates whether or not this location has active inventory. | |
AssignedLocationLocationId | String | Globally unique identifier. | |
AssignedLocationLocationIsActive | Boolean | Whether the location is active. | |
AssignedLocationLocationLegacyResourceId | Long | The ID of the corresponding resource in the REST Admin API. | |
AssignedLocationLocationName | String | The name of the location. | |
AssignedLocationLocationShipsInventory | Boolean | Indicates whether or not this location is used for calculating shipping rates. | |
DestinationAddress1 | String | The first line of the address of the destination. | |
DestinationAddress2 | String | The second line of the address of the destination. | |
DestinationCity | String | The city of the destination. | |
DestinationCompany | String | The company of the destination. | |
DestinationCountryCode | String | The two-letter country code of the destination. | |
DestinationEmail | String | The email of the customer at the destination. | |
DestinationFirstName | String | The first name of the customer at the destination. | |
DestinationId | String | Globally unique identifier for destination location. | |
DestinationLastName | String | The last name of the customer at the destination. | |
DestinationPhone | String | The phone number of the customer at the destination. | |
DestinationProvince | String | The province of the destination. | |
destinationZip | String | The ZIP code of the destination. | |
OrderId | String | Globally unique identifier for Orders. | |
RequestStatus | String | The date and time when the customer was last updated. | |
Status | String | Whether the customer has agreed to receive marketing materials. |
Fulfillment represents a shipment of one or more items in an order.
The Cloud processes filters client-side within the Cloud.
The following queries are processed server-side:
SELECT * FROM Fulfillments
SELECT * FROM Fulfillments WHERE OrderId='gid://shopify/Order/1234'
SELECT * FROM Fulfillments WHERE OrderId IN ('gid://shopify/Order/1234', 'gid://shopify/Order/1235')
Name | Type | References | Description |
Id [KEY] | String | Globally unique identifier. | |
CreatedAt | Datetime | The date and time when the fulfillment was created. | |
DeliveredAt | Datetime | The date that this fulfillment was delivered. | |
DisplayStatus | String | Human readable display status for this fulfillment. | |
EstimatedDeliveryAt | Datetime | The estimated date that this fulfillment will arrive. | |
InTransitAt | Datetime | The date and time when the fulfillment went into transit. | |
LegacyResourceId | Long | The ID of the corresponding resource in the REST Admin API. | |
Name | String | Human readable reference identifier for this fulfillment. | |
OrderId | String | Globally unique identifier. | |
RequiresShipping | Boolean | Whether any of the line items in the fulfillment require shipping. | |
Status | String | The status of the fulfillment. | |
TotalQuantity | Integer | Sum of all line item quantities for the fulfillment. | |
UpdatedAt | Datetime | The date and time when the fulfillment was last modified. |
Lists fulfillmentServices which Represents service that prepares and ships orders on behalf of the store owner.
The Cloud processes filters client-side within the Cloud.
Name | Type | References | Description |
Id [KEY] | String | The ID of the fulfillment service. | |
CallbackUrl | String | The callback URL the fulfillment service has registered for requests. | |
FulfillmentOrdersOptIn | Boolean | Whether the fulfillment service has opted into fulfillment order based requests. | |
Handle | String | Human-readable unique identifier for this fulfillment service. | |
InventoryManagement | Boolean | Whether the fulfillment service tracks product inventory and provides updates to Shopify. | |
LocationActivatable | Boolean | Whether this location can be reactivated. | |
LocationAddressAddress1 | String | The first line of the address for the location. | |
LocationAddressAddress2 | String | The second line of the address for the location. | |
LocationAddressCity | String | The city of the location. | |
LocationAddressCountry | String | The country of the location. | |
LocationAddressCountryCode | String | The two-letter country code of the location. | |
LocationAddressLatitude | Decimal | The latitude coordinates of the location. | |
LocationAddressLongitude | Decimal | The longitude coordinates of the location. | |
LocationAddressPhone | String | The phone number of the location. | |
LocationAddressProvince | String | The province of the location. | |
LocationAddressProvinceCode | String | The code for the region of the address, such as the province, state, or district. | |
LocationAddressZip | String | The ZIP code of the location. | |
LocationAddressVerified | Boolean | Whether the location address has been verified. | |
LocationDeactivatable | Boolean | Whether this location can be deactivated. | |
LocationDeactivatedAt | String | Date and time the location was deactivated (null if location is still active). | |
LocationDeletable | Boolean | Whether this location can be deleted. | |
LocationFulfillsOnlineOrders | Boolean | Indicates whether this location can fulfill online orders. | |
LocationHasActiveInventory | Boolean | Indicates whether or not this location has active inventory. | |
LocationHasUnfulfilledOrders | Boolean | Indicates whether or not this location has unfulfilled orders. | |
LocationId | String | Globally unique identifier. | |
LocationIsActive | Boolean | Whether the location is active. | |
LocationLegacyResourceId | Long | The ID of the corresponding resource in the REST Admin API. | |
LocationName | String | The name of the location. | |
LocationshipsInventory | Boolean | Indicates whether or not this location is used for calculating shipping rates. | |
LocationSuggestedAddressesAddress1 | String | The first line of the suggested address. | |
LocationSuggestedAddressesAddress2 | String | The second line of the suggested address. | |
LocationSuggestedAddressesCity | String | The city of the suggested address. | |
LocationSuggestedAddressesCountry | String | The country of the suggested address. | |
LocationSuggestedAddressesCountryCode | String | The country code of the suggested address. | |
LocationSuggestedAddressesFormatted | String | A formatted version of the suggested address. | |
LocationSuggestedAddressesProvince | String | The province of the suggested address. | |
LocationSuggestedAddressesProvinceCode | String | The province code of the suggested address. | |
LocationSuggestedAddressesZip | String | The ZIP code of the suggested address. | |
ProductBased | Boolean | Whether the fulfillment service supports local deliveries. | |
ServiceName | String | The name of the fulfillment service as seen by merchants. | |
ShippingMethodsCode | String | A unique code associated with the rate. | |
ShippingMethodsLabel | String | A description of the rate, which customers will see at checkout. | |
Type | String | Type associated with the fulfillment service. |
Retrieves a list of metafields that belong to one or many resource IDs.
The Cloud uses the Shopify API to process search criteria that refer to the Id, OwnerId and OwnerResource columns. The Cloud processes other filters client-side within the Cloud.
OwnerResource is a required column, and eligible values include "product" and "variant".
The following columns correspond to the same name columns in the REST table, except for Id and LegacyResourceId:
Id corresponds to REST.Metafields.AdminGraphqlApiId, and LegacyResourceId corresponds to REST.Metafields.Id. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.
For example, the following queries are processed server side:
SELECT * FROM Metafields WHERE OwnerResource = 'product'
SELECT * FROM Metafields WHERE OwnerResource = 'product' AND Id = 'gid://shopify/Metafield/19334473318423'
SELECT * FROM Metafields WHERE OwnerId = 'gid://shopify/ProductVariant/39378393497623' AND OwnerResource = 'variant'
SELECT * FROM Metafields WHERE OwnerId IN ('gid://shopify/ProductVariant/39378393497623','gid://shopify/Product/1418248224791') AND OwnerResource = 'variant'
Name | Type | References | Description |
Id [KEY] | String | The unique ID of the metafield. | |
LegacyResourceId | Long | The ID of the corresponding resource in the REST Admin API. | |
Namespace | String | A container for a set of metafields. You need to define a custom namespace for your metafields to distinguish them from the metafields used by other apps. | |
Key | String | The name of the metafield. | |
Value | String | The information to be stored as metadata. | |
Type | String | The metafield's information type. | |
Description | String | A description of the information that the metafield contains. | |
OwnerId | String | The ID of the resource that the metafield is attached to. | |
OwnerResource | String | The type of resource that the metafield is attached to.
The allowed values are product, variant. | |
CreatedAt | Datetime | The date and time when the metafield was created. | |
UpdatedAt | Datetime | The date and time when the metafield was last updated. |
The order risks associated with this order.
The Cloud uses the Shopify API to process search criteria that refer to the Id column. The Cloud processes other filters client-side within the Cloud. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.
For example, the following queries are processed server side:
SELECT * FROM Orders
SELECT * FROM Orders WHERE Id='123'
Name | Type | References | Description |
OrderId [KEY] | String | The order which this order risk is associated. | |
Display | Boolean | Whether the risk level is shown in the Shopify admin. If false, then this order risk is ignored when Shopify determines the overall risk level for the order. | |
Level | String | The likelihood that an order is fraudulent, based on this order risk. The level can be set by Shopify risk analysis or by an app. | |
Message | String | The risk message that's shown to the merchant in the Shopify admin. |
Tax rates with their effective dates of application
The Cloud uses the Shopify API to process search criteria that refer to the Id, CreatedAt, CustomerId, DiscountCode, Email, Name, ProcessedAt, RiskLevel, Test, UpdatedAt columns. The supported SQL operators are '=' and 'IN' for Id, '=' and '!=' for CustomerId, DiscountCode, Email, Name, RiskLevel and Test, '=','!=','>','<','<=','>=' for the CreatedAt, ProcessedAt, and UpdatedAt columns. The Cloud processes other filters client-side within the Cloud. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.
For example, the following queries are processed server side:
SELECT * FROM Orders
SELECT * FROM Orders WHERE Id='gid://shopify/Order/232220695'
SELECT * FROM Orders WHERE Id IN ('gid://shopify/Order/232220695', 'gid://shopify/Order/23220695')
SELECT * FROM Orders WHERE CustomerId='3514040471'
SELECT * FROM Orders WHERE CustomerId!='3514040471'
SELECT * FROM Orders WHERE CustomerId='3514040471' AND Test=true
SELECT * FROM Orders WHERE CustomerId='3514040471' AND RiskLevel='LOW'
SELECT * FROM Orders WHERE CustomerId='3514040471' AND RiskLevel!='LOW'
SELECT * FROM Orders WHERE CustomerId='3514040471' AND Email='test@cdata.com'
SELECT * FROM Orders WHERE CustomerId='3514040471' AND Email!='test@cdata.com'
SELECT * FROM Orders WHERE CustomerId='3514040471' AND DiscountCode='BlackFriday'
SELECT * FROM Orders WHERE CustomerId='3514040471' AND DiscountCode!='BlackFriday'
SELECT * FROM Orders WHERE CustomerId='3514040471' AND UpdatedAt='2022-01-18 02:32:26.0'
SELECT * FROM Orders WHERE CustomerId='3514040471' AND UpdatedAt!='2022-01-18 02:32:26.0'
SELECT * FROM Orders WHERE CustomerId='3514040471' AND UpdatedAt<='2022-01-18 02:32:26.0'
SELECT * FROM Orders WHERE CustomerId='3514040471' AND UpdatedAt>='2022-01-18 02:32:26.0' AND UpdatedAt<='2022-05-10 07:52:22.0'
SELECT * FROM Orders WHERE CustomerId='3514040471' AND ProcessedAt='2017-09-19 06:10:39.0'
SELECT * FROM Orders WHERE CustomerId='3514040471' AND ProcessedAt!='2017-09-19 06:10:39.0'
SELECT * FROM Orders WHERE CustomerId='3514040471' AND ProcessedAt<='2017-09-19 06:10:39.0'
SELECT * FROM Orders WHERE CustomerId='3514040471' AND ProcessedAt>='2017-09-19 06:10:39.0' AND ProcessedAt<='2021-10-07 23:45:38.0'
SELECT * FROM Orders WHERE CustomerId='3514040471' AND CreatedAt='2017-09-19 06:10:39.0'
SELECT * FROM Orders WHERE CustomerId='3514040471' AND CreatedAt!='2017-09-19 06:10:39.0'
SELECT * FROM Orders WHERE CustomerId='3514040471' AND CreatedAt<='2017-09-19 06:10:39.0'
SELECT * FROM Orders WHERE CustomerId='3514040471' AND CreatedAt>='2017-09-19 06:10:39.0' AND CreatedAt<='2021-10-07 23:45:38.0'
Name | Type | References | Description |
Id [KEY] | String | Globally unique identifier. | |
BillingAddressMatchesShippingAddress | Boolean | Whether the billing address matches the shipping address. | |
BillingAddress1 | String | The first line of the address. Typically the street address or PO Box number. | |
BillingAddress2 | String | The second line of the address. Typically the number of the apartment, suite, or unit. | |
BillingAddressCity | String | The name of the city, district, village, or town. | |
BillingAddressCompany | String | The name of the customer's company or organization. | |
BillingAddressCountry | String | The name of the country. | |
BillingAddressCountryCodeV2 | String | The two-letter code for the country of the address. For example, US. | |
BillingAddressFirstName | String | The first name of the customer. | |
BillingAddressFormattedArea | String | A comma-separated list of the values for city, province, and country. | |
BillingAddressId | String | Globally unique identifier. | |
BillingAddressLastName | String | The last name of the customer. | |
BillingAddressLatitude | Double | The latitude coordinate of the customer address. | |
BillingAddressLongitude | Double | The longitude coordinate of the customer address. | |
BillingAddressName | String | The full name of the customer, based on firstName and lastName. | |
BillingAddressPhone | String | A unique phone number for the customer. Formatted using E.164 standard. For example, +16135551111. | |
BillingAddressProvince | String | The region of the address, such as the province, state, or district. | |
BillingAddressProvinceCode | String | The two-letter code for the region. For example, ON. | |
BillingAddressZip | String | The zip or postal code of the address. | |
CanMarkAsPaid | Boolean | Whether the order can be manually marked as paid. | |
CanNotifyCustomer | Boolean | Whether notifications can be sent to the customer or not. | |
CancelReason | String | Reason the order was canceled. Returns null if the order wasn't canceled. | |
CancelledAt | Datetime | Date and time when the order was canceled. Returns null if the order wasn't canceled. | |
Capturable | Boolean | Whether payment for the order can be captured. Returns true when the customer's credit card has been authorized for payment and the authorization period has not expired. | |
CartDiscountPresentmentMoneyAmount | Decimal | The amount of the order-level discount in presentment currency. | |
CartDiscountPresentmentCurrencyCode | String | The presentment currency of the order-level discount. | |
CartDiscountShopMoneyAmount | Decimal | The amount of the order-level discount in shop currency. | |
CartDiscountShopCurrencyCode | String | The shop currency of the order-level discount. | |
ClientIp | String | The ip address of the client that is associated with this order. | |
Closed | Boolean | Whether the order is closed. | |
ClosedAt | Datetime | Date and time when the order closed. If the order is not closed, then this field is null. | |
Confirmed | Boolean | Whether inventory has been reserved for the order. | |
CreatedAt | Datetime | Date and time when the order was created in Shopify. | |
CurrencyCode | String | The currency of the store at the time of the order. If payment hasn't occurred, then this field is null. | |
CustomerId | String | Unique identifier of the customer who placed the order. Not all orders have customers associated with them. | |
CustomerFirstName | String | The first name of the customer who placed the order. Not all orders have customers associated with them. | |
CustomerLastName | String | The last name of the customer who placed the order. Not all orders have customers associated with them. | |
CustomerAcceptsMarketing | Boolean | Whether the customer agreed to receive marketing materials. | |
CustomerLocale | String | A two-letter or three-letter language code, optionally followed by a region modifier. Example values could be 'en', 'en-CA', 'en-PIRATE'. | |
DiscountCode | String | Discount code provided by the customer. | |
DisplayAddress1 | String | The first line of the address. Typically the street address or PO Box number. | |
DisplayAddress2 | String | The second line of the address. Typically the number of the apartment, suite, or unit. | |
DisplayAddressCity | String | The name of the city, district, village, or town. | |
DisplayAddressCompany | String | The name of the customer's company or organization. | |
DisplayAddressCountry | String | The name of the country. | |
DisplayAddressCountryCodeV2 | String | The two-letter code for the country of the address. For example, US. | |
DisplayAddressFirstName | String | The first name of the customer. | |
DisplayAddressFormattedArea | String | A comma-separated list of the values for city, province, and country. | |
DisplayAddressId | String | Globally unique identifier. | |
DisplayAddressLastName | String | The last name of the customer. | |
DisplayAddressLatitude | Double | The latitude coordinate of the customer address. | |
DisplayAddressLongitude | Double | The longitude coordinate of the customer address. | |
DisplayAddressName | String | The full name of the customer, based on firstName and lastName. | |
DisplayAddressPhone | String | A unique phone number for the customer. Formatted using E.164 standard. For example, +16135551111. | |
DisplayAddressProvince | String | The region of the address, such as the province, state, or district. | |
DisplayAddressProvinceCode | String | The two-letter code for the region. For example, ON. | |
DisplayAddressZip | String | The zip or postal code of the address. | |
DisplayFinancialStatus | String | Financial status of the order that can be shown to the merchant. This field does not capture all the possible details of an order's financial state and should only be used for display summary purposes. | |
DisplayFulfillmentStatus | String | Fulfillment status for the order that can be shown to the merchant. This field does not capture all the possible details of an order's fulfillment state. It should only be used for display summary purposes. | |
Edited | Boolean | Whether the order has had any edits applied or not. | |
String | Email address provided by the customer. | ||
Fulfillable | Boolean | Whether there are items that can be fulfilled. After an order is completely fulfilled (or completely refunded without any fulfillments) then this field returns false. | |
FullyPaid | Boolean | Whether the order has been paid in full. | |
HasTimelineComment | Boolean | Whether the merchant added timeline comments to the order. | |
LegacyResourceId | Long | The ID of the corresponding resource in the REST Admin API. | |
MerchantEditable | Boolean | Whether the order can be edited or not. | |
Name | String | Unique identifier for the order that appears on the order. For example, #1000 or _Store1001. This value is not unique across multiple stores. | |
NetPaymentPresentmentMoneyAmount | Decimal | The amount of the net payment for the order in presentment currency. | |
NetPaymentPresentmentCurrencyCode | String | The presentment currency of the net payment for the order. | |
NetPaymentShopMoneyAmount | Decimal | The amount of the net payment for the order in shop currency. | |
NetPaymentShopCurrencyCode | String | The shop currency of the net payment for the order. | |
Note | String | Contents of the note associated with the order. | |
OriginalTotalPricePresentmentMoneyAmount | Decimal | The amount of the total price of the order prior to any applied edits in presentment currency. | |
OriginalTotalPricePresentmentCurrencyCode | String | The presentment currency of the total price of the order prior to any applied edits. | |
OriginalTotalPriceShopMoneyAmount | Decimal | The amount of the total price of the order prior to any applied edits in shop currency. | |
OriginalTotalPriceShopCurrencyCode | String | The shop currency of the total price of the order prior to any applied edits. | |
PaymentCollectionDetails | String | The payment collection details for an order requiring additional payment. | |
Phone | String | Phone number provided by the customer. | |
PresentmentCurrencyCode | String | The payment currency of the customer for this order. | |
ProcessedAt | Datetime | Date and time when the order was processed. When orders are imported from an app, this date and time may not match the date and time when the order was created. | |
PublicationId | String | The Id of the publication that created the order. | |
RefundDiscrepancyPresentmentMoneyAmount | Decimal | The amount of the difference between suggested and actual refund amounts in presentment currency. | |
RefundDiscrepancyPresentmentCurrencyCode | String | The presentment currency of the difference between suggested and actual refund amounts. | |
RefundDiscrepancyShopMoneyAmount | Decimal | The amount of the difference between suggested and actual refund amounts in shop currency. | |
RefundDiscrepancyShopCurrencyCode | String | The shop currency of the difference between suggested and actual refund amounts. | |
Refundable | Boolean | Whether the order can be refunded. | |
RequiresShipping | Boolean | Whether any line item in the order requires physical shipping. | |
Restockable | Boolean | Whether the order can be restocked. | |
RiskLevel | String | Fraud risk level of the order. | |
ShippingAddress1 | String | The first line of the address. Typically the street address or PO Box number. | |
ShippingAddress2 | String | The second line of the address. Typically the number of the apartment, suite, or unit. | |
ShippingAddressCity | String | The name of the city, district, village, or town. | |
ShippingAddressCompany | String | The name of the customer's company or organization. | |
ShippingAddressCountry | String | The name of the country. | |
ShippingAddressCountryCodeV2 | String | The two-letter code for the country of the address. For example, US. | |
ShippingAddressFirstName | String | The first name of the customer. | |
ShippingAddressFormattedArea | String | A comma-separated list of the values for city, province, and country. | |
ShippingAddressId | String | Globally unique identifier. | |
ShippingAddressLastName | String | The last name of the customer. | |
ShippingAddressLatitude | Double | The latitude coordinate of the customer address. | |
ShippingAddressLongitude | Double | The longitude coordinate of the customer address. | |
ShippingAddressName | String | The full name of the customer, based on firstName and lastName. | |
ShippingAddressPhone | String | A unique phone number for the customer. Formatted using E.164 standard. For example, +16135551111. | |
ShippingAddressProvince | String | The region of the address, such as the province, state, or district. | |
ShippingAddressProvinceCode | String | The two-letter code for the region. For example, ON. | |
ShippingAddressZip | String | The zip or postal code of the address. | |
SubtotalLineItemsQuantity | Integer | The sum of the quantities for the line items that contribute to the order's subtotal. | |
SubtotalPricePresentmentMoneyAmount | Decimal | The amount of the subtotal of the line items and their discounts in presentment currency. | |
SubtotalPricePresentmentCurrencyCode | String | The presentment currency of the subtotal of the line items and their discounts. | |
SubtotalPriceShopMoneyAmount | Decimal | The amount of the subtotal of the line items and their discounts in shop currency. | |
SubtotalPriceShopCurrencyCode | String | The shop currency of the subtotal of the line items and their discounts. | |
TaxesIncluded | Boolean | Whether taxes are included in the subtotal price of the order. | |
Test | Boolean | Whether the order is a test. Test orders are made using the Shopify Bogus Gateway or the Shopify Payments test mode. | |
TotalCapturablePresentmentMoneyAmount | Decimal | The amount authorized for the order, that is uncaptured or undercaptured in presentment currency. | |
TotalCapturablePresentmentCurrencyCode | String | The presentment currency of the amount authorized for the order, that is uncaptured or undercaptured. | |
TotalCapturableShopMoneyAmount | Decimal | The amount authorized for the order, that is uncaptured or undercaptured in shop currency. | |
TotalCapturableShopCurrencyCode | String | The shop currency of the amount authorized for the order, that is uncaptured or undercaptured. | |
TotalDiscountPresentmentMoneyAmount | Decimal | The total amount discounted from the order (includes order-level and line item discounts) in presentment currency. | |
TotalDiscountPresentmentCurrencyCode | String | The presentment currency of the total amount discounted from the order (includes order-level and line item discounts). | |
TotalDiscountShopMoneyAmount | Decimal | The total amount discounted from the order (includes order-level and line item discounts) in shop currency. | |
TotalDiscountShopCurrencyCode | String | The shop currency of the total amount discounted from the order (includes order-level and line item discounts). | |
TotalOutstandingPresentmentMoneyAmount | Decimal | The total amount of money not yet authorized for the order in presentment currency. | |
TotalOutstandingPresentmentCurrencyCode | String | The presentment currency of the total amount of money not yet authorized for the order. | |
TotalOutstandingShopMoneyAmount | Decimal | The total amount of money not yet authorized for the order in shop currency. | |
TotalOutstandingShopCurrencyCode | String | The shop currency of the total amount of money not yet authorized for the order. | |
TotalPricePresentmentMoneyAmount | Decimal | The total amount of the order (includes taxes and discounts) in presentment currency. | |
TotalPricePresentmentCurrencyCode | String | The presentment currency of the total amount of the order (includes taxes and discounts). | |
TotalPriceShopMoneyAmount | Decimal | The total amount of the order (includes taxes and discounts) in shop currency. | |
TotalPriceShopCurrencyCode | String | The shop currency of the total amount of the order (includes taxes and discounts). | |
TotalReceivedPresentmentMoneyAmount | Decimal | The total amount received by the customer for the order in presentment currency. | |
TotalReceivedPresentmentCurrencyCode | String | The presentment currency of the total amount received by the customer for the order. | |
TotalReceivedShopMoneyAmount | Decimal | The total amount received by the customer for the order in shop currency. | |
TotalReceivedShopCurrencyCode | String | The shop currency of the total amount received by the customer for the order. | |
TotalRefundedPresentmentMoneyAmount | Decimal | The total amount refunded for the order in presentment currency. | |
TotalRefundedPresentmentCurrencyCode | String | The presentment currency of the total amount refunded for the order. | |
TotalRefundedShopMoneyAmount | Decimal | The total amount refunded for the order in shop currency. | |
TotalRefundedShopCurrencyCode | String | The shop currency of the total amount refunded for the order. | |
TotalRefundedShippingPresentmentMoneyAmount | Decimal | The total amount refunded for shipping in presentment currency. | |
TotalRefundedShippingPresentmentCurrencyCode | String | The presentment currency of the total amount refunded for shipping. | |
TotalRefundedShippingShopMoneyAmount | Decimal | The total amount refunded for shipping in shop currency. | |
TotalRefundedShippingShopCurrencyCode | String | The shop currency of the total amount refunded for shipping. | |
TotalShippingPresentmentMoneyAmount | Decimal | The total amount charged for shipping in presentment currency. | |
TotalShippingPresentmentCurrencyCode | String | The presentment currency of the total amount charged for shipping. | |
TotalShippingShopMoneyAmount | Decimal | The total amount charged for shipping in shop currency. | |
TotalShippingShopCurrencyCode | String | The shop currency of the total amount charged for shipping. | |
TotalTaxPresentmentMoneyAmount | Decimal | The total of all taxes applied to the order in presentment currency. | |
TotalTaxPresentmentCurrencyCode | String | The presentment currency of the total of all taxes applied to the order. | |
TotalTaxShopMoneyAmount | Decimal | The total of all taxes applied to the order in shop currency. | |
TotalTaxShopCurrencyCode | String | The shop currency of the total of all taxes applied to the order. | |
TotalWeight | Long | Total weight (grams) of the order. | |
Unpaid | Boolean | Whether no payments have been made for the order. If no payments have been made for the order, then this returns true. | |
UpdatedAt | Datetime | Date and time when the order was last modified. |
List of all transactions associated with the orders.
The Cloud uses the Shopify API to process search criteria that refer to the OrderId column. The Cloud processes other filters client-side within the Cloud.
The Shopify APIs requires the search criteria to contain OrderId. For example, the following query is processed server side.
SELECT * FROM OrderTransactions WHERE OrderId = '123'
When performing a query without a filter or a filter other then the OrderId column, the Cloud will retrieve the entire list of transactions and perform the filters client-side.
The Shopify API requires that an OrderId be specified when retrieving transactions. Therefore to retrieve all the transactions, the Cloud will first retrieve all the OrderIds
from the Orders table (essentially performing the query: SELECT Id FROM Orders). Once the OrderId values have been retrieved, the Cloud will issue individual requests to the
Shopify API to retrieve all the transactions for each OrderId to build the result set.
SELECT * FROM OrderTransactions
SELECT * FROM OrderTransactions WHERE Kind='Capture'
You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false. Other search criteria will be ignored and an unfiltered response will be returned.
Name | Type | References | Description |
Id [KEY] | String | Globally unique identifier. | |
OrderId | String | The Id of the associated order. | |
AccountNumber | String | The masked account number associated with the payment method. | |
AmountSetPresentmentMoneyAmount | Decimal | The amount of the transaction in presentment currency. | |
AmountSetPresentmentMoneyCurrencyCode | String | The currency of the transaction in presentment currency. | |
AmountSetShopMoneyAmount | Decimal | The amount of the transaction in shop currency. | |
AmountSetShopMoneyCurrencyCode | String | The currency of the transaction in shop currency. | |
AuthorizationCode | String | Authorization code associated with the transaction. | |
CreatedAt | Datetime | Date and time when the transaction was created. | |
ErrorCode | String | A standardized error code, independent of the payment provider. | |
FormattedGateway | String | The human-readable payment gateway name used to process the transaction. | |
Gateway | String | The payment gateway used to process the transaction. | |
Kind | String | The kind of transaction. | |
ManuallyCapturable | Boolean | Whether the transaction can be manually captured. | |
MaximumRefundableMoneyAmount | Decimal | The available amount with currency to refund on the gateway. | |
MaximumRefundableMoneyCurrencyCode | String | The currency of the refund on the gateway. | |
ParentTransactionId | String | The associated parent transaction, for example the authorization of a capture. | |
ProcessedAt | Datetime | Date and time when the transaction was processed. | |
Status | String | The status of this transaction. | |
Test | Boolean | Whether the transaction is a test transaction. | |
TotalUnsettledSetPresentmentMoneyAmount | Decimal | The available amount to capture on the gateway in presentment currency. | |
TotalUnsettledSetPresentmentMoneyCurrencyCode | String | The available money currency to capture on the gateway in presentment currency. | |
TotalUnsettledSetShopMoneyAmount | Decimal | The available amount to capture on the gateway in shop currency. | |
TotalUnsettledSetShopMoneyCurrencyCode | String | The available money currency to capture on the gateway in shop currency. |
The images associated with the product.
The Cloud uses the Shopify API to process search criteria that refer to the ProductId column. The Cloud processes other filters client-side within the Cloud.
You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.
For example, the following queries are processed server side:
SELECT * FROM ProductImages WHERE ProductId = 'gid://shopify/Product/1234567890123'
SELECT * FROM ProductImages WHERE ProductId IN ('gid://shopify/Product/1234567890123', 'gid://shopify/Product/1234567890124')
Name | Type | References | Description |
Id [KEY] | String | A unique identifier for the image. | |
ProductId | String |
Products.Id | A globally-unique identifier. |
AltText | String | A word or phrase to share the nature or contents of an image. | |
Height | Int | The original height of the image in pixels. Returns 'null' if the image is not hosted by Shopify. | |
Width | Int | The original width of the image in pixels. Returns 'null' if the image is not hosted by Shopify. | |
Url | String | The location of the image as a URL. |
A list of product options. The limit is specified by Shop.resourceLimits.maxProductOptions.
The Cloud uses the Shopify API to process search criteria that refer to the ProductId column. The Cloud processes other filters client-side within the Cloud.
You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.
For example, the following queries are processed server side:
SELECT * FROM ProductOptions WHERE ProductId = 'gid://shopify/Product/1234567890123'
SELECT * FROM ProductOptions WHERE ProductId IN ('gid://shopify/Product/1234567890123', 'gid://shopify/Product/1234567890124')
Name | Type | References | Description |
Id [KEY] | String | A globally-unique identifier. | |
ProductId | String |
Products.Id | A globally-unique identifier. |
Name | String | The product option's name. | |
Position | Int | The product option's position. | |
Values | String | The corresponding value to the product option name. |
List of products.
The Cloud uses the Shopify API to process search criteria that refer to the following columns:
The Cloud processes other filters client-side within the Cloud.
Note that server-side filter extends to =/!= for Bool/String type columns, and only includes other operators for datetime or number type columns.
You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.
For example, the following queries are processed server side:
SELECT * FROM Products WHERE Id = 'gid://shopify/Product/6830135181335'
SELECT * FROM Products WHERE Id IN ('gid://shopify/Product/6830135181335', 'gid://shopify/Product/6830135181336')
SELECT * FROM Products WHERE Title = 'title'
SELECT * FROM Products WHERE Vendor = 'vendor'
SELECT * FROM Products WHERE CreatedAt > '2022-11-02T09:07:53.000-04:00'
Name | Type | References | Description |
Id | String | A globally-unique identifier. | |
LegacyResourceId | Long | The ID of the corresponding resource in the REST Admin API. | |
Description | String | The description of the product, complete with HTML formatting. | |
DescriptionHtml | String | The description of the product, complete with HTML formatting. | |
Title | String | The title of the product. | |
Handle | String | A unique human-friendly string of the product's title. | |
ProductType | String | The product type specified by the merchant. | |
Tags | String | A comma separated list of tags associated with the product. Updating 'tags' overwrites any existing tags that were previously added to the product. | |
Status | String | The product status. This controls visibility across all channels. | |
Vendor | String | The name of the product's vendor. | |
OnlineStorePreviewUrl | String | The online store preview URL. | |
OnlineStoreUrl | String | The online store URL for the product.A value of 'null' indicates that the product is not published to the Online Store sales channel. | |
RequiresSellingPlan | Bool | Whether the product can only be purchased with a selling plan (subscription). Products that are sold on subscription ('requiresSellingPlan: true') can be updated only for online stores. If you update a product to be subscription only, then the product is unpublished from all channels except the online store. | |
SellingPlanGroupCount | Int | Count of selling plan groups associated with the product. | |
TracksInventory | Bool | Whether inventory tracking has been enabled for the product. | |
TotalInventory | Int | The quantity of inventory in stock. | |
HasOnlyDefaultVariant | Bool | Whether the product has only a single variant with the default option and value. | |
HasOutOfStockVariants | Bool | Whether the product has out of stock variants. | |
TotalVariants | Int | The number of variants that are associated with the product. | |
TemplateSuffix | String | The theme template used when viewing the product in a store. | |
GiftCardTemplateSuffix | String | The theme template used when viewing the gift card in a store. | |
IsGiftCard | Bool | Whether the product is a gift card. | |
PublishedAt | Datetime | The date and time when the product was published to the Online Store. | |
UpdatedAt | Datetime | The date and time when the product was last modified.A product's 'updatedAt' value can change for different reasons. For example, if an order is placed for a product that has inventory tracking set up, then the inventory adjustment is counted as an update. | |
CreatedAt | Datetime | The date and time when the product was created. | |
FeedbackSummary | String | Summary of resource feedback pertaining to the resource. | |
PriceRangeMaxVariantPriceAmount | Decimal | Decimal money amount. | |
PriceRangeMaxVariantPriceCurrencyCode | String | Currency of the money. | |
PriceRangeMinVariantPriceAmount | Decimal | Decimal money amount. | |
PriceRangeMinVariantPriceCurrencyCode | String | Currency of the money. | |
SeoTitle | String | SEO Title. | |
SeoDescription | String | SEO Description. | |
MediaCount | Int | Total count of media belonging to a product. | |
FeaturedImageId | String | A unique identifier for the image. | |
FeaturedImageAltText | String | A word or phrase to share the nature or contents of an image. | |
FeaturedImageHeight | Int | The original height of the image in pixels. Returns 'null' if the image is not hosted by Shopify. | |
FeaturedImageWidth | Int | The original width of the image in pixels. Returns 'null' if the image is not hosted by Shopify. | |
FeaturedImageUrl | String | The location of the image as a URL. | |
ResourcePublicationOnCurrentPublicationIsPublished | Bool | Whether the resource publication is published. If true, then the resource publication is published to the publication. If false, then the resource publication is staged to be published to the publication. | |
ResourcePublicationOnCurrentPublicationPublishDate | Datetime | The date that the resource publication was or is going to be published to the publication. | |
ResourcePublicationOnCurrentPublicationPublicationId | String | A globally-unique identifier. | |
ResourcePublicationOnCurrentPublicationPublicationName | String | Name of the publication. | |
ResourcePublicationOnCurrentPublicationPublicationSupportsFuturePublishing | Bool | Whether the publication supports future publishing. |
List of the product variants.
The Cloud uses the Shopify API to process search criteria that refer to the following columns:
The Cloud processes other filters client-side within the Cloud. Note that server-side filter extends to =/!= for Bool/String type columns, and only includes other operators for datetime or number type columns.
You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria will be ignored and an unfiltered response will be returned.
For example, the following queries are processed server side:
SELECT * FROM ProductVariants WHERE Id = 'gid://shopify/ProductVariant/6788079091735'
SELECT * FROM ProductVariants WHERE Id IN ('gid://shopify/ProductVariant/6830135181335', 'gid://ProductVariant/Product/6830135181336')
SELECT * FROM ProductVariants WHERE Title = 'title'
SELECT * FROM ProductVariants WHERE CreatedAt > '2018-02-05T15:47:32.000-05:00'
Name | Type | References | Description |
Id | String | A globally-unique identifier. | |
LegacyResourceId | Long | The ID of the corresponding resource in the REST Admin API. | |
ProductId | String |
Products.Id | A globally-unique identifier. |
Position | Int | The order of the product variant in the list of product variants. The first position in the list is 1. | |
AvailableForSale | Bool | Whether the product variant is available for sale. | |
SellableOnlineQuantity | Int | The total sellable quantity of the variant for online channels. This doesn't represent the total available inventory or capture (limitations based on customer location). | |
SellingPlanGroupCount | Int | Count of selling plan groups associated with the product variant. | |
Price | Decimal | The price of the product variant in the default shop currency. | |
CompareAtPrice | Decimal | The compare-at price of the variant in the default shop currency. | |
Title | String | The title of the product variant. | |
DisplayName | String | Display name of the variant, based on product's title + variant's title. | |
Barcode | String | The value of the barcode associated with the product. | |
Weight | Float | The weight of the product variant in the unit system specified with weight_unit. | |
WeightUnit | String | The unit of measurement that applies to the product variant's weight. If you don't specify a value for weight_unit, then the shop's default unit of measurement is applied. Valid values: 'g', 'kg', 'oz', 'lb'. | |
Sku | String | An identifier for the product variant in the shop. Required in order to connect to a fulfillment service. | |
Taxable | Bool | Whether a tax is charged when the product variant is sold. | |
TaxCode | String | The tax code for the product variant. | |
UpdatedAt | Datetime | The date and time when the product variant was last modified. | |
CreatedAt | Datetime | The date and time when the variant was created. | |
InventoryPolicy | String | Whether customers are allowed to place an order for the product variant when it's out of stock. | |
InventoryQuantity | Int | The total sellable quantity of the variant. | |
InventoryItemId | String | A globally-unique identifier. | |
InventoryItemLegacyResourceId | String | The ID of the corresponding resource in the REST Admin API. | |
InventoryItemSku | String | Inventory item SKU. | |
InventoryItemDuplicateSkuCount | Int | The number of inventory items that share the same SKU with this item. | |
InventoryItemHarmonizedSystemCode | String | The harmonized system code of the item. | |
InventoryItemLocationsCount | Int | The number of locations where this inventory item is stocked. | |
InventoryItemRequiresShipping | Bool | Whether the inventory item requires shipping. | |
InventoryItemCountryCodeOfOrigin | String | The ISO 3166-1 alpha-2 country code of where the item originated from. | |
InventoryItemProvinceCodeOfOrigin | String | The ISO 3166-2 alpha-2 province code of where the item originated from. | |
InventoryItemUnitCostAmount | Decimal | Decimal money amount. | |
InventoryItemUnitCostCurrencyCode | String | Currency of the money. | |
InventoryItemTracked | Bool | Whether inventory levels are tracked for the item. | |
InventoryItemTrackedEditableLocked | Bool | Whether the attribute is locked for editing. | |
InventoryItemTrackedEditableReason | String | The reason the attribute is locked for editing. | |
InventoryItemInventoryHistoryUrl | String | The URL that points to the inventory history for the item. | |
InventoryItemCreatedAt | Datetime | The date and time when the inventory item was created. | |
InventoryItemUpdatedAt | Datetime | The date and time when the inventory item was updated. | |
ImageId | String | A unique identifier for the image. | |
ImageAltText | String | A word or phrase to share the nature or contents of an image. | |
ImageHeight | Int | The original height of the image in pixels. Returns 'null' if the image is not hosted by Shopify. | |
ImageWidth | Int | The original width of the image in pixels. Returns 'null' if the image is not hosted by Shopify. | |
ImageUrl | String | The location of the image as a URL. | |
DeliveryProfileId | String | A globally-unique identifier. | |
DeliveryProfileName | String | The name of the delivery profile. | |
DeliveryProfileDefault | Bool | Whether this is the default profile. | |
DeliveryProfileLegacyMode | Bool | Whether this shop has enabled legacy compatibility mode for delivery profiles. | |
DeliveryProfileActiveMethodDefinitionsCount | Int | The number of active shipping rates for the profile. | |
DeliveryProfileOriginLocationCount | Int | The number of active origin locations for the profile. | |
DeliveryProfileZoneCountryCount | Int | The number of countries with active rates to deliver to. | |
DeliveryProfileLocationsWithoutRatesCount | Int | The number of locations without rates defined. | |
DeliveryProfileProductVariantsCountCount | Int | The product variant count. | |
DeliveryProfileProductVariantsCountCapped | Bool | Whether the count has reached the cap of 500. | |
FulfillmentServiceEditableLocked | Bool | Whether the attribute is locked for editing. | |
FulfillmentServiceEditableReason | String | The reason the attribute is locked for editing. |
Represents a refund of items or transactions in an order.
The Cloud processes filters client-side within the Cloud.
Name | Type | References | Description |
Id [KEY] | String | Globally unique identifier. | |
CreatedAt | Datetime | When the refund was created. | |
DutiesCountryCodeOfOrigin | Datetime | The ISO code of the country of origin of the line item. | |
DutiesHarmonizedSystemCode | Datetime | The harmonized system code of the line item. | |
DutiesId | String | Globally unique identifier. | |
DutiesTaxlineRate | Decimal | The tax rate to be applied. | |
DutiesTaxlineratePercentage | Decimal | The percentage of the price that the tax rate represents. | |
DutiesTaxlineTitle | String | The name of the tax. | |
LegacyResourceId | Long | The ID of the corresponding resource in the REST Admin API. | |
TotalRefundedSetPresentmentMoneyAmount | Decimal | Amount in presentment currency. | |
TotalRefundedSetPresentmentMoneyCurrencyCode | String | Amount in shop currency. | |
TotalRefundedSetShopMoneyAmount | Decimal | Amount in presentment currency. | |
TotalRefundedSetShopMoneyCurrencyCode | String | Amount in shop currency. | |
Note | String | Note associated with the refund. | |
OrderId | String | Order ID associated with the refund. | |
UpdatedAt | Datetime | When the refund was last updated. |
Stored procedures are function-like interfaces that extend the functionality of the Cloud beyond simple SELECT/INSERT/UPDATE/DELETE operations with Shopify.
Stored procedures accept a list of parameters, perform their intended function, and then return, if applicable, any relevant response data from Shopify, along with an indication of whether the procedure succeeded or failed.
Name | Description |
Gets an authentication token from Shopify.
Name | Type | Description |
AuthMode | String | The type of authentication mode to use. Select App for getting authentication tokens via a desktop app. Select Web for getting authentication tokens via a Web app.
The allowed values are APP, WEB. The default value is APP. |
CallbackUrl | String | The URL the user will be redirected to after authorizing your application. This value must match the Redirect URL you have specified in the Shopify app settings. Only needed when the Authmode parameter is Web. |
Verifier | String | The verifier returned from Shopify after the user has authorized your app to have access to their data. This value will be returned as a parameter to the callback URL. |
State | String | Encodes state of the app, which will be returned verbatim in the response and can be used to match the response up to a given request. |
Scope | String | The scope or permissions you are requesting.
The default value is read_products,write_products,read_product_listings,read_customers,write_customers,read_orders,write_orders,read_draft_orders,write_draft_orders,read_inventory,write_inventory,read_locations,read_fulfillments,write_fulfillments,read_assigned_fulfillment_orders,read_merchant_managed_fulfillment_orders,read_third_party_fulfillment_orders,read_shipping,write_shipping,read_price_rules,write_price_rules,read_discounts,write_discounts,read_marketing_events,write_marketing_events,read_shopify_payments_payouts. |
Name | Type | Description |
OAuthAccessToken | String | The access token used for communication with Shopify. |
ExpiresIn | String | The remaining lifetime on the access token. A -1 denotes that it will not expire. |
Gets the authorization URL that must be opened separately by the user to grant access to your application. Only needed when developing Web apps. You will request the OAuthAccessToken from this URL.
Name | Type | Description |
CallbackUrl | String | The URL the user will be redirected to after authorizing your application. This value must match the Redirect URL in the Shopify app settings. |
State | String | Encodes state of the app, which will be returned verbatim in the response and can be used to match the response up to a given request. |
Scope | String | The scope or permissions you are requesting.
The default value is read_products,write_products,read_product_listings,read_customers,write_customers,read_orders,write_orders,read_draft_orders,write_draft_orders,read_inventory,write_inventory,read_locations,read_fulfillments,write_fulfillments,read_assigned_fulfillment_orders,read_merchant_managed_fulfillment_orders,read_third_party_fulfillment_orders,read_shipping,write_shipping,read_price_rules,write_price_rules,read_discounts,write_discounts,read_marketing_events,write_marketing_events,read_shopify_payments_payouts. |
Name | Type | Description |
URL | String | The authorization URL, entered into a Web browser to obtain the verifier token and authorize your app. |
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 Shopify:
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 Customers table:
SELECT ColumnName, DataTypeName FROM sys_tablecolumns WHERE TableName='Customers'
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 SendInvite stored procedure:
SELECT * FROM sys_procedureparameters WHERE ProcedureName='SendInvite' 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 Customers table:
SELECT * FROM sys_keycolumns WHERE IsKey='True' AND TableName='Customers'
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 primary keys.
The following query retrieves the primary keys from all tables and views:
SELECT * FROM sys_primarykeys
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. |
KeySeq | String | The sequence number of the primary key. |
KeyName | String | The name of the primary 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:shopify: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 |
ShopURL | The full URL of your Shopify shop. |
AuthScheme | The type of authentication to use when connecting to Shopify. |
AccessToken | Admin API Access Token for a custom app. |
Property | Description |
OAuthClientId | The client Id assigned when you register your application with an OAuth authorization server. |
OAuthClientSecret | The client secret assigned when you register your application with an OAuth authorization server. |
OAuthAccessToken | The access token for connecting using OAuth. |
CallbackURL | The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings. |
OAuthVerifier | The verifier code returned from the OAuth authorization URL. |
OAuthExpiresIn | The lifetime in seconds of the OAuth AccessToken. |
OAuthTokenTimestamp | The Unix epoch timestamp in milliseconds when the current Access Token was created. |
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. |
Schema | Specify the Shopify server version to connect with. |
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 |
UseBulkAPI | Specifies whether to use Shopify Bulk Operations for GraphQL schema. |
BulkPageSize | The number of records to retrieve before returning results to the user when UseBulkAPI=true. |
IncludeCustomFields | Set whether to include custom fields in the query to the Products table. When off, performance will increase, but you can not get and change Products custom fields. |
MaxPointsPerCall | Specifies how many points a call will cost. |
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. |
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. |
Readonly | You can use this property to enforce read-only access to Shopify from the provider. |
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 Authentication properties you can configure in the connection string for this provider.
Property | Description |
ShopURL | The full URL of your Shopify shop. |
AuthScheme | The type of authentication to use when connecting to Shopify. |
AccessToken | Admin API Access Token for a custom app. |
The full URL of your Shopify shop.
string
""
The full URL of your Shopify shop.
The type of authentication to use when connecting to Shopify.
string
"OAuth"
Admin API Access Token for a custom app.
string
""
Admin API Access Token for a custom app.
This section provides a complete list of the OAuth properties you can configure in the connection string for this provider.
Property | Description |
OAuthClientId | The client Id assigned when you register your application with an OAuth authorization server. |
OAuthClientSecret | The client secret assigned when you register your application with an OAuth authorization server. |
OAuthAccessToken | The access token for connecting using OAuth. |
CallbackURL | The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings. |
OAuthVerifier | The verifier code returned from the OAuth authorization URL. |
OAuthExpiresIn | The lifetime in seconds of the OAuth AccessToken. |
OAuthTokenTimestamp | The Unix epoch timestamp in milliseconds when the current Access Token was created. |
The client Id assigned when you register your application with an OAuth authorization server.
string
""
As part of registering an OAuth application, you will receive the OAuthClientId value, sometimes also called a consumer key, and a client secret, the OAuthClientSecret.
The client secret assigned when you register your application with an OAuth authorization server.
string
""
As part of registering an OAuth application, you will receive the OAuthClientId, also called a consumer key. You will also receive a client secret, also called a consumer secret. Set the client secret in the OAuthClientSecret property.
The access token for connecting using OAuth.
string
""
The OAuthAccessToken property is used to connect using OAuth. The OAuthAccessToken is retrieved from the OAuth server as part of the authentication process. It has a server-dependent timeout and can be reused between requests.
The access token is used in place of your user name and password. The access token protects your credentials by keeping them on the server.
The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings.
string
""
During the authentication process, the OAuth authorization server redirects the user to this URL. This value must match the callback URL you specify in your app settings.
The verifier code returned from the OAuth authorization URL.
string
""
The verifier code returned from the OAuth authorization URL. This can be used on systems where a browser cannot be launched such as headless systems.
See to obtain the OAuthVerifier value.
Set OAuthSettingsLocation along with OAuthVerifier. When you connect, the Cloud exchanges the OAuthVerifier for the OAuth authentication tokens and saves them, encrypted, to the specified file.
Once the OAuth settings file has been generated, you can remove OAuthVerifier from the connection properties and connect with OAuthSettingsLocation set.
To automatically refresh the OAuth token values, set OAuthSettingsLocation and additionally set InitiateOAuth to REFRESH.
The lifetime in seconds of the OAuth AccessToken.
string
""
Pair with OAuthTokenTimestamp to determine when the AccessToken will expire.
The Unix epoch timestamp in milliseconds when the current Access Token was created.
string
""
Pair with OAuthExpiresIn to determine when the AccessToken will expire.
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 Shopify 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. |
Schema | Specify the Shopify server version to connect with. |
A path to the directory that contains the schema files defining tables, views, and stored procedures.
string
"%APPDATA%\\CData\\Shopify 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.
Note: Given that this Cloud supports multiple schemas, the structure for Shopify custom schema files is as follows:
If left unspecified, the default location is "%APPDATA%\\CData\\Shopify 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.
Specify the Shopify server version to connect with.
string
"REST"
Set this property to REST to use the Shopify REST API or GRAPHQL to use the Shopify GraphQL API.
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\\Shopify Data Provider"
The CacheLocation is a simple, file-based cache.
If left unspecified, the default location is "%APPDATA%\\CData\\Shopify 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 Shopify 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 |
UseBulkAPI | Specifies whether to use Shopify Bulk Operations for GraphQL schema. |
BulkPageSize | The number of records to retrieve before returning results to the user when UseBulkAPI=true. |
IncludeCustomFields | Set whether to include custom fields in the query to the Products table. When off, performance will increase, but you can not get and change Products custom fields. |
MaxPointsPerCall | Specifies how many points a call will cost. |
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. |
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. |
Readonly | You can use this property to enforce read-only access to Shopify from the provider. |
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. |
Specifies whether to use Shopify Bulk Operations for GraphQL schema.
bool
false
This property determines whether or not to use Shopify Bulk Operations for GraphQL schema. The driver automatically creates bulk query jobs and returns results as they are available.
The number of records to retrieve before returning results to the user when UseBulkAPI=true.
int
1000
The BulkPageSize indicates the number of records that are returned at a time when reading the data. In the background, the CData Cloud continues to read additional results from the data source. This connection property only has an impact when UseBulkAPI is set to true.
Set whether to include custom fields in the query to the Products table. When off, performance will increase, but you can not get and change Products custom fields.
bool
false
Set whether to include custom fields in the query to the Products table. When off, performance will increase, but you can not get and change Products custom fields.
Specifies how many points a call will cost.
string
"50"
The total cost of your queries cannot exceed 1,000 points at any given time, and that room is created in the app's bucket at a rate of 50 points per second.
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.
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, "*=*".
You can use this property to enforce read-only access to Shopify from the provider.
bool
false
If this property is set to true, the Cloud will allow only SELECT queries. INSERT, UPDATE, DELETE, and stored procedure queries will cause an error to be thrown.
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 Customers 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"