The C1DataEngine Tool or Command Line Tool provides a command line interface for managing C1DataEngine workspaces. It performs the following tasks:

• Creates base tables by importing data from external sources.
• Creates and executes named queries against the base tables.
• Returns the results of a named query, or the contents of a base table.
• Deletes the results of a named query, or the contents of a base table.

The tool is used by the C1DataEngine Workbench, an extension for Visual Studio Code. It can also be used to isolate workspace initialization and data import operations from programs that execute queries on the imported data.

Note that the C1DataEngine Tool requires .NET Core 2.2.

Installation

If you are installing C1DataEngine Tool for .NET Core for the first time, run the following code at the command prompt:

dotnet tool install --global C1.DataEngine.Tool

After the tool has been installed, the c1dataengine command will be available. Run c1dataengine --help to see the available options.

But if you are upgrading C1DataEngine Tool, you have to uninstall it first. Run the following code at the command prompt:

dotnet tool uninstall --global C1.DataEngine.Tool
dotnet tool install --global C1.DataEngine.Tool

Importing Data

The Command Line tool assumes that the current directory is either empty or contains a previously initialized C1DataEngine workspace ﴾with a manifest file named metadata.xml﴿. To initialize a workspace, start with an empty directory and import one or more base tables.

To import a base table from an existing data source, run the following command:

c1dataengine table <Name> --provider <Provider> --connect <ConnectionString> --command <CommandText>

This is an example code for SQL Server:

c1dataengine table MyTable --connect
"server=mydomain.database.windows.net;user=***;password=***;database=master"
--command "select * from TableName"

This is an example code for JSON (array format):

c1dataengine table MyTable --provider json --connect "URI=file.json;JSONPATH=
[*]"

This is an example code for JSON (object format, where items is the name of the property containing the items array):

c1dataengine table MyTable --provider json --connect
"URI=file.json;JSONPATH=$..items[*]"

This is an example code for CSV files:

c1dataengine table MyTable --provider csv --connect
"URI=file.csv;LineDelimiter='\n'"

Since the command lines can be long and difficult to type correctly, the C1DataEngine tool supports response files for input. In the previous example, the response file would look like this:

table
MyTable
--provider
csv
--connect
URI=file.csv;LineDelimiter='\n'

Then, the command line becomes:

c1dataengine @response.txt

Response files are valid for all command types. The lines beginning with # are treated as comments. Blank lines are not  however allowed.

Executing Queries

To create and execute a query, run the following command:

c1dataengine query <Name> --table <Table> --column <Column> --join <Join> --range <Range>

Since queries can be complex, they are best suited for the response file format. For example, here is a basic query that aggregates a product table by color and counts the number of products of a given color:

query
ProductsByColor
--table
Products
--column
ProductColor
--column
Count=Count(ProductId)

Note that you can include as many column descriptors as required. The general format of a column descriptor is:

name
alias=name
alias=operation(name)

Only the name portion of the descriptor is required. You can use the alias qualifier to specify a different name for the column. The operation is an aggregate function such as Count, Sum, Avg, First, Last, Min, or Max. Note that the Aggregate function names are case‐sensitive.

If you want to restrict the query to products costing at least $100, you can add the following two lines to the response file:

--range
ProductPrice=Gte(100)

Other comparison functions commonly used in range expressions are Lte and Eq. String arguments need not be enclosed in quotes. Note that the Comparison function names are case‐sensitive.

When using the full command line format, you may need to enclose aggregate column descriptors in quotation marks depending upon the type of shell program you are using. For example, if you are using bash on macOS, the last argument in the following example code snippet needs to be quoted:

c1dataengine query ProductsByColor --table Products --column ProductColor --column "Count=Count(ProductId)"

To specify a join between two tables, you must provide an expression that indicates how they are related, as in the following example:

query
OrderSummary
--table
Orders
--table
OrderDetails
--join
Orders.OrderId=OrderDetails.OrderId
--column
Orders.OrderId
--column
Total=Sum(OrderDetails.Price)

Note that the join expression and column descriptors use table‐qualified names, since multiple tables participate in this query.

Returning Results

To return the results from a query, or the contents of a base table, run the following command:

c1dataengine result <Name> --format <Format>

Although you can only create queries from base tables, you can use the results of a query to formulate a new base table. Further, you can use it to build the sub-queries. For example:

c1dataengine result Query1 > Query1.csv
c1dataengine table Query1Base --provider csv --connect
"URI=Query1.csv;LineDelimiter='\n'"

Query1Base can now be used as the base table for one or more queries.

Deleting a Base Table or Query

To delete an existing base table or query result set, run the following command:

c1dataengine delete <Name>

This command deletes any intermediate queries that may have been created for range and/or join expressions.