This page provides steps to quickly get started with ECL. It is assumed that the reader is already familiar with SQL and providing a comparative functionality in ECL will help drive home the points quicker.
Data Set Definition
SQL
In SQL, a data set is defined as a table. A CREATE TABLE is used to create the definition of a table. In addition, SQL is schema compliant. By this we mean that a definition of the table has to exist before it can be populated with data. This is in contrast to ECL where ECL is schema less.
An SQL Table Definition:
| Code Block |
|---|
|
CREATE TABLE Products (
productCode VARCHAR(15) NOT NULL,
productName VARCHAR(70) NOT NULL,
productLine VARCHAR(50) NOT NULL,
productScale VARCHAR(10) NOT NULL,
productVendor VARCHAR(50) NOT NULL,
productDescription TEXT NOT NULL,
quantityInStock SMALLINT NOT NULL,
buyPrice DOUBLE NOT NULL,
MSRP DOUBLE NOT NULL,
PRIMARY KEY (productCode)
); |
ECL
The key concept to keep in mind here is that ECL is schema less. It has been designed to be schema less to provide the maximum flexibity to manipulate the data. A logical file or data set in ECL is simply data residing in one or more physical files on disk. The ECL program can decide at runtime to interpret the schema in a certain way. The following is an example:
| Code Block |
|---|
Product := RECORD
STRING productCode;
STRING100 productName;
STRING productLine;
STRING productScale;
STRING productVendor;
STRING productDescription;
INTEGER quantityInStock;
DECIMAL7_2 buyPrice;
DECIMAL7_2 MSRP;
END;
products := DATASET('~cm::products', Product , csv); |
As you can see, in ECL, the DATASET definition refers to an existing logical file "~cm::products" of TYPE Product. This is backwards compared to SQL where a schema definition has to exist before any data can be populated. Schema less systems like HPCC and the ECL language help in providing the maximum flexibility to ingest structured, semi-structured and unstructured data in a massively parallel fashion.
Basic Query
SQL
| Code Block |
|---|
|
select * from products |
ECL
Outputting Specific Fields
SQL
| Code Block |
|---|
select productCode, productName from products |
ECL
| Code Block |
|---|
OUTPUT(products, {productCode, productName}); |
Filtering Data
SQL
| Code Block |
|---|
|
select * from products where productCode='S32_1374' |
ECL
| Code Block |
|---|
OUTPUT(products(productCode='S32_1374'));
//OR
OUTPUT(
products(
Std.Str.ToUpperCase(productCode)
='S32_1374')
); |
SORTING
SQL
| Code Block |
|---|
select * from products order by productName |
ECL
| Code Block |
|---|
OUTPUT(SORT(products, productName)); |
Counting
SQL
| Code Block |
|---|
select COUNT(*) from products where UPPER(productLine)='VINTAGE CARS' |
ECL
| Code Block |
|---|
OUTPUT(
COUNT(
products(
Std.Str.ToUpperCase(productLine)
='VINTAGE CARS'))
); |
Grouping
SQL
| Code Block |
|---|
select * from products group by productLine |
ECL
| Code Block |
|---|
OUTPUT(
GROUP(
SORT(products, productLine),
productLine)
); |
Aggregation
SQL
| Code Block |
|---|
select SUM(buyPrice) from products
select AVG(buyPrice) from products
select MAX(buyPrice) from products |
ECL
| Code Block |
|---|
OUTPUT(
SUM(products, buyPrice)
);
OUTPUT(
AVE(products, buyPrice)
);
OUTPUT(
MAX(products, buyPrice)
); |
Crosstab
SQL
| Code Block |
|---|
Select productLine, COUNT(*) from products group by productLine |
ECL
| Code Block |
|---|
OUTPUT(
TABLE(products,
{
productLine;
productLineCount:= COUNT(GROUP);
}, productLine)
); |
