Index management

Sample data

The sample data set used in this documentation originates from the meter_gen data generator that was developed by GridPocket in the context of the IOStack project. It generates electricity, water, and gas meter readings, along with their associated timestamps, geospatial locations, and additional information. The data set is in Parquet format, has 9 GB, and is publicly available to use with Data Engine at cos:\\us-geo\sql\metergen. The queries that are listed in the examples are also available in the UI under Samples > Index management. Examples for queries when you work on tables are available under Samples > Index management on tables. In the UI samples, under Basic index creation, you find a fast running index creation example that creates only MinMax indexes. Advanced index creation is an example for using all index types and it takes longer to finish.

Assigning a base location for data skipping indexes

Indexes are stored in Cloud Object Storage in a bucket of your choice. Before you start to create indexes, first configure the default location, called base location, for indexes created in your Data Engine instance. When you use Hive tables, you can also configure the index location per Hive table.

To assign your base location, use the following command:

ALTER METAINDEX SET LOCATION <path>

The following command includes a path:

ALTER METAINDEX SET LOCATION cos://us-south/mybucket/mypath/

Creating data skipping indexes

When you create a data skipping index on a data set, decide which columns to index, and choose an index type for each column. Your choices depend on your workload and data. In general, index those columns that are queried the most in the WHERE clause. The three supported index types are MinMax, ValueList, and BloomFilter.

The following example creates a data skipping index on the metergen data set that uses three index types:

CREATE METAINDEX
MINMAX FOR temp,
MINMAX FOR lat,
MINMAX FOR lng,
BLOOMFILTER FOR vid,
VALUELIST FOR city
ON cos://us-geo/sql/metergen STORED AS parquet

In the Cloud Object Storage URI, specify the top level (the root) of the data set.

It is possible to share indexes across Data Engine accounts. Users who have READ access to the base location of an index can use it by setting their base location. However, it is important to avoid multiple users who write indexes for the same data set to the same base location. Users can avoid sharing indexes by using different base locations.

Data Engine charges for index creation based on the amount of data scanned. Index creation for Parquet files benefits from the availability of schema information and the possibility of column projection to limit the amount of data scanned. However, index creation for JSON and CSV files requires schema inference that results in extra passes on the input data. To avoid extra passes on the data, create a table in the catalog for the respective data to provide the required schema information.

Describing an index

To retrieve data skipping index statistics and metadata, use the DESCRIBE operation, as in the following example:

DESCRIBE METAINDEX
ON cos://us-geo/sql/metergen STORED AS parquet

The result includes how many objects were indexed, whether the index is up to date, the base location of the indexes, and the index types that were generated.

Using data skipping indexes

When you create a data skipping index, Data Engine automatically uses it when it runs queries. The UI shows the percentage of skipped objects. You also find examples in the UI of queries that benefit from data skipping.

Geospatial data skipping

Data skipping is supported for queries that use geospatial functions.

The following list shows supported geospatial functions:

• ST_Distance
• ST_Intersects
• ST_Contains
• ST_Equals
• ST_Crosses
• ST_Touches
• ST_Within
• ST_Overlaps
• ST_EnvelopesIntersect
• ST_IntersectsInterior

SELECT * FROM cos://us-geo/sql/metergen STORED AS PARQUET WHERE
ST_Distance(ST_Point(lng,lat),ST_WKTToSQL('POINT(6.433881 43.422323)')) < 1000.0

CREATE METAINDEX
GEOSPATIAL FOR location
ON cos://us-geo/sql/hospitals_geometry.parquet STORED AS parquet

SELECT name
FROM cos://us-geo/sql/hospitals_geometry.parquet STORED AS PARQUET
WHERE ST_Intersects(ST_WKTToSQL(location), ST_Buffer(ST_WKTToSQL('POINT (-74.0 42.0)'), 46800.0))

Choosing data formats

You can use data skipping with all of the formats that are supported by Data Engine, except for AVRO. It is best for data layout to use a column-based format, such as Parquet. To infer the schema for CSV and JSON, before you run any queries, the entire data set must first be scanned. Scanning the entire data set is not necessary if you create tables by using the Data Engine catalog. Unlike Parquet and ORC, CSV and JSON do not have built-in data skipping capabilities and can potentially benefit more from data skipping.

Refreshing data skipping indexes

If data is added to a data set, or if modifications are made to a data set after the creation of a data skipping index, the new or changed data is not skipped during queries. When the amount of new data becomes significant, refresh the index incrementally, as follows:

REFRESH METAINDEX
ON cos://us-geo/sql/metergen STORED AS parquet

Showing data skipping indexes

To retrieve existing data skipping indexes under the currently configured metadata base location, use the SHOW operation, as in the following example:

SHOW METAINDEXES

The result includes the currently set metadata base location and a list of indexed data sets.

If you see DEPRECATED_SUPPORTED in front of the index, it means that the metadata version is deprecated but supported and skipping works. The next REFRESH updates the metadata automatically.

Deleting data skipping indexes

To delete a data skipping index, use the following query:

DROP METAINDEX
ON cos://us-geo/sql/metergen STORED AS parquet

Dropping a table does not also drop the table indexes.

Notes

The metadata for a partitioned table must be different from the metadata on the physical location (the location that was defined in the LOCATION clause of the CREATE TABLE query) because the table can contain partitions that are not located under the physical location. Therefore, depending on what data skipping metadata was generated for each case, you can get different results by using the table name than by using the Cloud Object Storage URI.