
# Migrating From Hive

openLooKeng uses ANSI SQL syntax and semantics, whereas Hive uses a SQL-like language called HiveQL.

## Use subscript for accessing a dynamic index of an array instead of a udf

The subscript operator in SQL supports full expressions, unlike Hive (which only supports constants). Therefore you can write queries like:

```sql
SELECT my_array[CARDINALITY(my_array)] as last_element
FROM ...
```

## Avoid out of bounds access of arrays

Accessing out of bounds elements of an array will result in an exception. You can avoid this with an `if` as follows:

```sql
SELECT IF(CARDINALITY(my_array) >= 3, my_array[3], NULL)
FROM ...
```

## Use ANSI SQL syntax for arrays

Arrays are indexed starting from 1, not from 0:

```sql
SELECT my_array[1] AS first_element
FROM ...
```

Construct arrays with ANSI syntax:

```sql
SELECT ARRAY[1, 2, 3] AS my_array
```

## Use ANSI SQL syntax for identifiers and strings

Strings are delimited with single quotes and identifiers are quoted with double quotes, not backquotes:

```sql
SELECT name AS "User Name"
FROM "7day_active"
WHERE name = 'foo'
```

## Quote identifiers that start with numbers

Identifiers that start with numbers are not legal in ANSI SQL and must be quoted using double quotes:

```sql
SELECT *
FROM "7day_active"
```

## Use the standard string concatenation operator

Use the ANSI SQL string concatenation operator:

```sql
SELECT a || b || c
FROM ...
```

## Use standard types for CAST targets

The following standard types are supported for `CAST` targets:

```sql
SELECT
  CAST(x AS varchar)
, CAST(x AS bigint)
, CAST(x AS double)
, CAST(x AS boolean)
FROM ...
```

In particular, use `VARCHAR` instead of `STRING`.

## Use CAST when dividing integers

openLooKeng follows the standard behavior of performing integer division when dividing two integers. For example, dividing `7` by `2` will result in `3`, not `3.5`. To perform floating point division on two integers, cast
one of them to a double:

```sql
SELECT CAST(5 AS DOUBLE) / 2
```

## Use WITH for complex expressions or queries

When you want to re-use a complex output expression as a filter, use either an inline subquery or factor it out using the `WITH` clause:

```sql
WITH a AS (
  SELECT substr(name, 1, 3) x
  FROM ...
)
SELECT *
FROM a
WHERE x = 'foo'
```

## Use UNNEST to expand arrays and maps

openLooKeng supports `unnest` for expanding arrays and maps. Use `UNNEST` instead of `LATERAL VIEW explode()`.

Hive query:

```sql
SELECT student, score
FROM tests
LATERAL VIEW explode(scores) t AS score;
```

openLooKeng query:

```sql
SELECT student, score
FROM tests
CROSS JOIN UNNEST(scores) AS t (score);
```
