Page tree
Skip to end of metadata
Go to start of metadata

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:

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:

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
select * from products 
ECL
products;

Outputting Specific Fields

SQL 
select productCode, productName from products
ECL
OUTPUT(products, {productCode, productName});

Filtering Data

SQL
select * from products where productCode='S32_1374' 
ECL
OUTPUT(products(productCode='S32_1374'));

//OR
 
OUTPUT(
products(
   Std.Str.ToUpperCase(productCode) 
     ='S32_1374')
      );

SORTING

SQL
select * from products order by productName
ECL
OUTPUT(SORT(products, productName));

Counting

SQL
select COUNT(*) from products where UPPER(productLine)='VINTAGE CARS'
ECL
OUTPUT(
 COUNT(
 products(
   Std.Str.ToUpperCase(productLine)
     ='VINTAGE CARS'))
);

Grouping

SQL
select * from products group by productLine
ECL
OUTPUT( 
  GROUP(
     SORT(products, productLine), 
     productLine)
);

Aggregation

SQL
select SUM(buyPrice) from products

select AVG(buyPrice) from products

select MAX(buyPrice) from products
ECL
OUTPUT(
  SUM(products, buyPrice)
);

OUTPUT(
AVE(products, buyPrice)
);

OUTPUT(
MAX(products, buyPrice)
);

Crosstab

SQL
Select productLine, COUNT(*) from products group by productLine
ECL
OUTPUT( 
  TABLE(products, 
  {
     productLine; 
     productLineCount:= COUNT(GROUP);
  }, productLine)
);

 

 

  • No labels