# Work with Command Line Tool ## Content 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 --provider --connect --command ```
| **Argument** | **Description** | **Default** | | --- | --- | --- | | Name | Required. Name of the base table to be created. | | | Provider | Optional. Data provider to use for import ﴾csv, sqlserver, json﴿. | sqlserver | | ConnectionString | Required. Connection string to use for the data provider. | | | CommandText | Optional. SQL command for the data provider. | select \* from DefaultTable | 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 --table --column --join --range ``` | **Argument** | **Description** | | --- | --- | | Name | Required. Name of the query to be created and executed. | | Table | Required. One or more base table names. | | Column | Required. One or more column descriptors. | | Join | Optional. Zero or more join expressions. | | Range | Optional. Zero or more range expressions. | 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 --format ```
| **Argument** | **Description** | **Default** | | --- | --- | --- | | Name | Required. Name of the base table or query. | | | Format | Optional. Data format to use for output ﴾csv, json﴿. Output is directed to stdout. | csv | 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 ```
| **Argument** | **Description** | | --- | --- | | Name | Required. Name of the base table or query to delete. | This command deletes any intermediate queries that may have been created for range and/or join expressions.