Skip to content

Commit f186d04

Browse files
joshuali925joshuali925
authored
Add cast function to PPL (#433)
Signed-off-by: Joshua Li <joshuali925@gmail.com>
1 parent 0fd7f2f commit f186d04

10 files changed

Lines changed: 142 additions & 6 deletions

File tree

‎.github/workflows/link-checker.yml‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -16,7 +16,7 @@ jobs:
1616
id: lychee
1717
uses: lycheeverse/lychee-action@master
1818
with:
19-
args: --accept=200,403,429,999 "./**/*.html" "./**/*.md" "./**/*.txt" --exclude "http://localhost*" "https://localhost" "https://odfe-node1:9200/" "https://community.tableau.com/docs/DOC-17978" ".*family.zzz" "https://pypi.python.org/pypi/opensearchsql/" "opensearch*" ".*@amazon.com" ".*email.com" "git@github.com" "http://timestamp.verisign.com/scripts/timstamp.dll"
19+
args: --accept=200,403,429,999 "./**/*.html" "./**/*.md" "./**/*.txt" --exclude "http://localhost*" "https://localhost" "https://odfe-node1:9200/" "https://community.tableau.com/docs/DOC-17978" ".*family.zzz" "https://pypi.python.org/pypi/opensearchsql/" "opensearch*" ".*@amazon.com" ".*email.com" "git@github.com" "http://timestamp.verisign.com/scripts/timstamp.dll" ".*/PowerBIConnector/bin/Release"
2020
env:
2121
GITHUB_TOKEN: ${{secrets.GITHUB_TOKEN}}
2222
- name: Fail if there were link errors

‎docs/user/ppl/functions/conversion.rst‎

Lines changed: 75 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,75 @@
1+
=========================
2+
Type Conversion Functions
3+
=========================
4+
5+
.. rubric:: Table of contents
6+
7+
.. contents::
8+
:local:
9+
:depth: 1
10+
11+
CAST
12+
----
13+
14+
Description
15+
>>>>>>>>>>>
16+
17+
Usage: cast(expr as dateType) cast the expr to dataType. return the value of dataType. The following conversion rules are used:
18+
19+
+------------+--------+--------+---------+-------------+--------+--------+
20+
| Src/Target | STRING | NUMBER | BOOLEAN | TIMESTAMP | DATE | TIME |
21+
+------------+--------+--------+---------+-------------+--------+--------+
22+
| STRING | | Note1 | Note1 | TIMESTAMP() | DATE() | TIME() |
23+
+------------+--------+--------+---------+-------------+--------+--------+
24+
| NUMBER | Note1 | | v!=0 | N/A | N/A | N/A |
25+
+------------+--------+--------+---------+-------------+--------+--------+
26+
| BOOLEAN | Note1 | v?1:0 | | N/A | N/A | N/A |
27+
+------------+--------+--------+---------+-------------+--------+--------+
28+
| TIMESTAMP | Note1 | N/A | N/A | | DATE() | TIME() |
29+
+------------+--------+--------+---------+-------------+--------+--------+
30+
| DATE | Note1 | N/A | N/A | N/A | | N/A |
31+
+------------+--------+--------+---------+-------------+--------+--------+
32+
| TIME | Note1 | N/A | N/A | N/A | N/A | |
33+
+------------+--------+--------+---------+-------------+--------+--------+
34+
35+
Note1: the conversion follow the JDK specification.
36+
37+
Cast to string example::
38+
39+
os> source=people | eval `cbool` = CAST(true as string), `cint` = CAST(1 as string), `cdate` = CAST(CAST('2012-08-07' as date) as string) | fields `cbool`, `cint`, `cdate`
40+
fetched rows / total rows = 1/1
41+
+---------+--------+------------+
42+
| cbool | cint | cdate |
43+
|---------+--------+------------|
44+
| true | 1 | 2012-08-07 |
45+
+---------+--------+------------+
46+
47+
Cast to number example::
48+
49+
os> source=people | eval `cbool` = CAST(true as int), `cstring` = CAST('1' as int) | fields `cbool`, `cstring`
50+
fetched rows / total rows = 1/1
51+
+---------+-----------+
52+
| cbool | cstring |
53+
|---------+-----------|
54+
| 1 | 1 |
55+
+---------+-----------+
56+
57+
Cast to date example::
58+
59+
os> source=people | eval `cdate` = CAST('2012-08-07' as date), `ctime` = CAST('01:01:01' as time), `ctimestamp` = CAST('2012-08-07 01:01:01' as timestamp) | fields `cdate`, `ctime`, `ctimestamp`
60+
fetched rows / total rows = 1/1
61+
+------------+----------+---------------------+
62+
| cdate | ctime | ctimestamp |
63+
|------------+----------+---------------------|
64+
| 2012-08-07 | 01:01:01 | 2012-08-07 01:01:01 |
65+
+------------+----------+---------------------+
66+
67+
Cast function can be chained::
68+
69+
os> source=people | eval `cbool` = CAST(CAST(true as string) as boolean) | fields `cbool`
70+
fetched rows / total rows = 1/1
71+
+---------+
72+
| cbool |
73+
|---------|
74+
| True |
75+
+---------+

‎docs/user/ppl/index.rst‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -72,6 +72,8 @@ The query start with search command and then flowing a set of command delimited
7272

7373
- `Relevance Functions <functions/relevance.rst>`_
7474

75+
- `Type Conversion Functions <functions/conversion.rst>`_
76+
7577
* **Optimization**
7678

7779
- `Optimization <../../user/optimization/optimization.rst>`_

‎ppl/src/main/antlr/OpenSearchPPLLexer.g4‎

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -91,6 +91,15 @@ DATAMODEL: 'DATAMODEL';
9191
LOOKUP: 'LOOKUP';
9292
SAVEDSEARCH: 'SAVEDSEARCH';
9393

94+
// CONVERTED DATA TYPES
95+
INT: 'INT';
96+
INTEGER: 'INTEGER';
97+
DOUBLE: 'DOUBLE';
98+
LONG: 'LONG';
99+
FLOAT: 'FLOAT';
100+
STRING: 'STRING';
101+
BOOLEAN: 'BOOLEAN';
102+
94103
// SPECIAL CHARACTERS AND OPERATORS
95104
PIPE: '|';
96105
COMMA: ',';
@@ -225,6 +234,7 @@ LEFT: 'LEFT';
225234
ASCII: 'ASCII';
226235
LOCATE: 'LOCATE';
227236
REPLACE: 'REPLACE';
237+
CAST: 'CAST';
228238

229239
// BOOL FUNCTIONS
230240
LIKE: 'LIKE';

‎ppl/src/main/antlr/OpenSearchPPLParser.g4‎

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -172,6 +172,7 @@ valueExpression
172172

173173
primaryExpression
174174
: evalFunctionCall
175+
| dataTypeFunctionCall
175176
| fieldExpression
176177
| literalValue
177178
;
@@ -226,11 +227,29 @@ evalFunctionCall
226227
: evalFunctionName LT_PRTHS functionArgs RT_PRTHS
227228
;
228229

230+
/** cast function */
231+
dataTypeFunctionCall
232+
: CAST LT_PRTHS expression AS convertedDataType RT_PRTHS
233+
;
234+
229235
/** boolean functions */
230236
booleanFunctionCall
231237
: conditionFunctionBase LT_PRTHS functionArgs RT_PRTHS
232238
;
233239

240+
convertedDataType
241+
: typeName=DATE
242+
| typeName=TIME
243+
| typeName=TIMESTAMP
244+
| typeName=INT
245+
| typeName=INTEGER
246+
| typeName=DOUBLE
247+
| typeName=LONG
248+
| typeName=FLOAT
249+
| typeName=STRING
250+
| typeName=BOOLEAN
251+
;
252+
234253
evalFunctionName
235254
: mathematicalFunctionBase
236255
| dateAndTimeFunctionBase

‎ppl/src/main/java/org/opensearch/sql/ppl/parser/AstExpressionBuilder.java‎

Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -14,7 +14,9 @@
1414
import static org.opensearch.sql.ppl.antlr.parser.OpenSearchPPLParser.BooleanLiteralContext;
1515
import static org.opensearch.sql.ppl.antlr.parser.OpenSearchPPLParser.BySpanClauseContext;
1616
import static org.opensearch.sql.ppl.antlr.parser.OpenSearchPPLParser.CompareExprContext;
17+
import static org.opensearch.sql.ppl.antlr.parser.OpenSearchPPLParser.ConvertedDataTypeContext;
1718
import static org.opensearch.sql.ppl.antlr.parser.OpenSearchPPLParser.CountAllFunctionCallContext;
19+
import static org.opensearch.sql.ppl.antlr.parser.OpenSearchPPLParser.DataTypeFunctionCallContext;
1820
import static org.opensearch.sql.ppl.antlr.parser.OpenSearchPPLParser.DecimalLiteralContext;
1921
import static org.opensearch.sql.ppl.antlr.parser.OpenSearchPPLParser.DistinctCountFunctionCallContext;
2022
import static org.opensearch.sql.ppl.antlr.parser.OpenSearchPPLParser.EvalClauseContext;
@@ -48,11 +50,13 @@
4850
import java.util.stream.Collectors;
4951
import org.antlr.v4.runtime.ParserRuleContext;
5052
import org.antlr.v4.runtime.RuleContext;
53+
import org.opensearch.sql.ast.dsl.AstDSL;
5154
import org.opensearch.sql.ast.expression.AggregateFunction;
5255
import org.opensearch.sql.ast.expression.Alias;
5356
import org.opensearch.sql.ast.expression.AllFields;
5457
import org.opensearch.sql.ast.expression.And;
5558
import org.opensearch.sql.ast.expression.Argument;
59+
import org.opensearch.sql.ast.expression.Cast;
5660
import org.opensearch.sql.ast.expression.Compare;
5761
import org.opensearch.sql.ast.expression.DataType;
5862
import org.opensearch.sql.ast.expression.Field;
@@ -233,6 +237,19 @@ public UnresolvedExpression visitEvalFunctionCall(EvalFunctionCallContext ctx) {
233237
.collect(Collectors.toList()));
234238
}
235239

240+
/**
241+
* Cast function.
242+
*/
243+
@Override
244+
public UnresolvedExpression visitDataTypeFunctionCall(DataTypeFunctionCallContext ctx) {
245+
return new Cast(visit(ctx.expression()), visit(ctx.convertedDataType()));
246+
}
247+
248+
@Override
249+
public UnresolvedExpression visitConvertedDataType(ConvertedDataTypeContext ctx) {
250+
return AstDSL.stringLiteral(ctx.getText());
251+
}
252+
236253
@Override
237254
public UnresolvedExpression visitRelevanceExpression(RelevanceExpressionContext ctx) {
238255
return new Function(

‎ppl/src/test/java/org/opensearch/sql/ppl/parser/AstExpressionBuilderTest.java‎

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -13,6 +13,7 @@
1313
import static org.opensearch.sql.ast.dsl.AstDSL.and;
1414
import static org.opensearch.sql.ast.dsl.AstDSL.argument;
1515
import static org.opensearch.sql.ast.dsl.AstDSL.booleanLiteral;
16+
import static org.opensearch.sql.ast.dsl.AstDSL.cast;
1617
import static org.opensearch.sql.ast.dsl.AstDSL.compare;
1718
import static org.opensearch.sql.ast.dsl.AstDSL.defaultFieldsArgs;
1819
import static org.opensearch.sql.ast.dsl.AstDSL.defaultSortFieldArgs;
@@ -467,6 +468,18 @@ public void testEvalFuncCallExpr() {
467468
));
468469
}
469470

471+
@Test
472+
public void testDataTypeFuncCall() {
473+
assertEqual("source=t | eval f=cast(1 as string)",
474+
eval(
475+
relation("t"),
476+
let(
477+
field("f"),
478+
cast(intLiteral(1), stringLiteral("string"))
479+
)
480+
));
481+
}
482+
470483
@Ignore("Nested field is not supported in backend yet")
471484
@Test
472485
public void testNestedFieldName() {

‎sql-odbc/CONTRIBUTING.md‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -97,6 +97,6 @@ If you discover a potential security issue in this project we ask that you notif
9797

9898
## Licensing
9999

100-
See the [LICENSE](./LICENSE) file for our project's licensing. We will ask you to confirm the licensing of your contribution.
100+
See the [LICENSE](./LICENSE.txt) file for our project's licensing. We will ask you to confirm the licensing of your contribution.
101101

102102
We may ask you to sign a [Contributor License Agreement (CLA)](http://en.wikipedia.org/wiki/Contributor_License_Agreement) for larger changes.

‎sql-odbc/README.md‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -94,7 +94,7 @@ If you discover a potential security issue in this project we ask that you notif
9494

9595
## Licensing
9696

97-
See the [LICENSE](./LICENSE) file for our project's licensing. We will ask you to confirm the licensing of your contribution.
97+
See the [LICENSE](./LICENSE.txt) file for our project's licensing. We will ask you to confirm the licensing of your contribution.
9898

9999
## Copyright
100100

‎sql-odbc/docs/user/power_bi_support.md‎

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -4,8 +4,8 @@
44
* Microsoft Power BI Desktop
55
* [OpenSearch](https://docs-beta.opensearch.org/opensearch/install/index/)
66
* [OpenSearch SQL ODBC driver](https://docs-beta.opensearch.org/search-plugins/sql/odbc/)
7-
* [OpenSearch.mez](../../src/PowerBIConnector/bin/Release/)
8-
* Optional: [sqlodbc_import.pbids](../../src/PowerBIConnector/PBIDSExamples) to help with repeated connections to the same server
7+
* [OpenSearch.mez](../../../bi-connectors/PowerBIConnector/bin/Release)
8+
* Optional: [sqlodbc_import.pbids](../../../bi-connectors/PowerBIConnector/PBIDSExamples) to help with repeated connections to the same server
99

1010
## Setup
1111
* Copy `OpenSearch.mez` file in the `<User>\Documents\Power BI Desktop\Custom Connectors\` folder. This will let Power BI access custom connector.
@@ -56,7 +56,7 @@
5656

5757
More info: https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-data-sources#using-pbids-files-to-get-data
5858

59-
Example PBIDS file for OpenSearch: (available here: [sqlodbc_import.pbids](../../src/PowerBIConnector/PBIDSExamples/sqlodbc_import.pbids))
59+
Example PBIDS file for OpenSearch: (available here: [sqlodbc_import.pbids](../../../bi-connectors/PowerBIConnector/PBIDSExamples/sqlodbc_import.pbids))
6060
```json
6161
{
6262
"version": "0.1",

0 commit comments

Comments
 (0)