Quick Start

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)
);

Page tree