The data structure design of the metadata parsed from COLUMN expression.

[brightcoder01]

COLUMN Expression -> Parse -> Metadata -> Code Generator -> Python Code for Transform & SQL for Analysis
The data structure of metadata is important to both the Parse stage and Code Generator stage.

PS: The data structure proposal will be attached later.

[brightcoder01]

The COLUMN expression in SQLFlow can be parsed to the metadata. The metadata is a DAG which describes the flow of data transform logic.
Let's take the following SQL expression for example:

SELECT *
FROM census_income
TO TRAIN WideAndDeepClassifier
COLUMN
    EMBEDDING(CONCAT(VOCABULARIZE(workclass), BUCKETIZE(capital_gain, num_buckets=5), BUCKETIZE(capital_loss, num_buckets=5), BUCKTIZE(hours_per_week, num_buckets=6)) AS group_1, 8),
    EMBEDDING(CONCAT(HASH(education), HASH(occupation), VOCABULARIZE(martial_status), VOCABULARIZE(relationship)) AS group_2, 8),
    EMBEDDING(CONCAT(BUCKETIZE(age, num_buckets=5), HASH(native_country), VOCABULARIZE(race), VOCABULARIZE(sex)) AS group_3, 8)
    FOR deep_embeddings
COLUMN
    EMBEDDING(group1, 1),
    EMBEDDING(group2, 1)
    FOR wide_embeddings
LABEL label

The parsed metadata DAG is just like the following graph. There are two outputs in this DAG deep_embeddings and wide_embeddings

In the graph above, there are two kinds of nodes.

• The rectangular node is TransformOP, it can be generated to a Keras preprocessing layer or a feature column api.

• The ellipse node is Source Feature Input, the in-degree of this node is always zero. It's mapped to an input layer.

We can check the generated code sample: Model with Feature Column and Model with Keras Preprocessing Layers.

[brightcoder01]

The workflow of COLUMN expression compilation

SQL Statement Example

SELECT *
FROM census_income
TO TRAIN WideAndDeepClassifier
COLUMN
    EMBEDDING(CONCAT(VOCABULARIZE(workclass), BUCKETIZE(capital_gain, num_buckets=5), BUCKETIZE(capital_loss, num_buckets=5), BUCKTIZE(hours_per_week, num_buckets=6)) AS group_1, 8),
    EMBEDDING(CONCAT(HASH(education), HASH(occupation), VOCABULARIZE(martial_status), VOCABULARIZE(relationship)) AS group_2, 8),
    EMBEDDING(CONCAT(BUCKETIZE(age, num_buckets=5), HASH(native_country), VOCABULARIZE(race), VOCABULARIZE(sex)) AS group_3, 8)
    FOR deep_embeddings
COLUMN
    EMBEDDING(group1, 1),
    EMBEDDING(group2, 1)
    FOR wide_embeddings
LABEL label

Parse COLUMN Expression into AST

Given the COLUMN expression, syntax parsing is naturally the first step. We will parse one COLUMN expression to one AST. For the SQL statement containing two column expressions above, we will parse it into two AST.

Convert the AST into a DAG of Transform Flow

Why is it a DAG instead of Tree?

1. We can have two or more column expressions, one column for one AST (tree structure). If we combine these ASTs, we will get a forest at fist.
2. In this expression, we have alias AS group1. The alias stands for a part of transform flow which are shared by multiple upstream nodes. Then the forest will become a DAG.

Expand the TransformOP

Let's take COLUMN Embedding(item_id, dimension=8) for example.
As we know, the SQLFlow statement is intention driven. We tell it to do embedding for the input feature item_id directly and don't tell how to map it into an integer id at first. And the latter is required for the model built by native TensorFlow. So we will expand the Embedding TransformOp and add the Hash to do id conversion. The DAG will be expaneded from item_id -> Embedding to item_id -> Hash -> Embedding.

Derive the parameters of TransformOP from data analysis

Each TransformOP has several parameters, a part of parameters are required. For example, boundaries is necessary for BUCKETIZE to generate the Python code. But we only have num_buckets from the COLUMN expression. The boundaries value will be calculated from the generated analysis SQL with the provided num_buckets information.

Derive the parameters of TransformOP from DAG Walkthrough

Some parameters of TransformOP can't be gotten from analysis.
For example, Embedding needs the parameter input_dimension, it's equals to num_buckets of the CONCAT which is the dependency of Embedding.
In one sentence, we need walk through the DAG and calculate the parameters of one TransformOP from its dependency.

Generate the Transform Python code from the DAG

Now we have a complete DAG: a complete Graph describing the dependency of TransformOPs and each TranformOP has all the required parameters.
Next we can generate the Python code for data transformation: Feature Column Version and Keras Preprocessing Layer Version.

[tonyyang-svail]

Information on the SQLFlow parser

SQLFlow parses column statement into S-expression, so it natural supports nested function calls like EMBEDDING(CONCAT(BUCKETIZE(...))). The tests might be useful examples of walking through the parsed S-expression. Converting S-expression to AST/DAG is relatively straight forward.

In this expression, we have alias AS group1. The alias stands for a part of transform flow which are shared by multiple upstream nodes. Then the forest will become a DAG.

SQLFlow parser hasn't support AS syntax. I can help with implementing this feature in parser if we find it useful. However, there are some design choices to make like how to deal with identical names, e.g. SELECT a, b, group1 FROM some_table TO TRAIN some_model COLUMN a as group1, b as group1, group1.

[tonyyang-svail]

Questions:

Expand the TransformOP: The DAG will be expaneded from item_id -> Embedding to item_id -> Hash -> Embedding.

How to determine the hash bucket size?

[brightcoder01]

Questions:

Expand the TransformOP: The DAG will be expaneded from item_id -> Embedding to item_id -> Hash -> Embedding.

How to determine the hash bucket size?

Just like the discussion in this issue, we will calculate the DISTINCT count of this feature from data analysis and get the recommended hash_bucket_size.

[brightcoder01]

SQLFlow parses column statement into S-expression, so it natural supports nested function calls like EMBEDDING(CONCAT(BUCKETIZE(...))). The tests might be useful examples of walking through the parsed S-expression. Converting S-expression to AST/DAG is relatively straight forward.

Thanks for providing help. :)

For the AS keyword, it standards for an alias inside COLUMN expression. It may be confusing to users if we mix the alias of standard SQL SELECT xxx FROM and COLUMN.

1. SELECT xxx FROM means a step to fetch data from source table. If we add alias group1 here, it means we will extract one more item group1 from the table by fetching the source table. But we are not doing this.
2. COLUMN xxx means the feature transformation inside the model.

It will be clearer if we decouple the standard SQL part SELECT * FROM xxx and the extended COLUMN expression.