Adding Secondary Indexes on JSON Tables

Describes how to add secondary indexes on HPE Data Fabric Database JSON tables.

About this task

You can add secondary indexes using the Control System, or the maprcli table index commands.

If you are adding an index on a large table, particularly if it contains complex data, you should consider modifying the mfs.db.parallel.copyregions parameter using the maprcli config save command. The parameter controls the degree of parallel processing in your HPE Data Fabric cluster. Increasing parallelism can improve the index build time by optimizing the build's intermediate copy operation.

Permissions

You need the following permissions to add an index:

  • readAce on the volume
  • lookupdir on directories in the table path
  • indexperm permission on the table

If you created the table in version 6.0 or later, you automatically have indexperm permission. For tables created before 6.0, even if you are the owner of the table, you must explicitly add indexperm permission.

NOTE
The mapr user is not treated as a superuser. HPE Data Fabric Database does not allow the mapr user to perform this operation unless that user is given the relevant permission or permissions with access-control expressions.

See Restrictions on Secondary Indexes for information about other restrictions.

NOTE
If you are configuring secondary index on a JSON table that has replication set up and the JSON table has row size of less than 1 KB, run the table edit command to reset the regionsizemb parameter to 512 MB from the default 4 GB value. This prevents a memory overflow during the table replication operation.

Adding Indexes Using the Control System

Procedure

  1. Log in to the Control System and go to the Indexes tab in the table information page.
  2. Click Add Index to display the Add Index page.
  3. Specify the following settings:
    1. Specify the name of the index in the Index Name field.
    2. Specify whether (Yes) or not (No) the index is hashed in the Hashed field. If Yes, specify the number of hash index partitions for distributing the keys.
      See Hashed Indexes for information about whether you should create a hashed index.
  4. Specify the list of indexed fields under FIELDS INDEXED.
    1. Specify the name of the indexed field in the Field Name text field.
    2. Select the ordering (Ascending or Descending) for the field from the Order drop-down menu.
    3. Select the function of the field from the Function drop-down menu.
      Before defining an index that specifies index keys with CAST functions, see Using Casts in Secondary Indexes for more information on creating indexes using CAST functions.
    TIP
    To add more indexed fields, click Add Another and repeat step 4.
  5. Specify the names of the included fields under INCLUDED FIELDS.
    For more information, see Covering Indexes.
    TIP
    To add additional included fields, click Add Another and repeat step 5.
  6. Click Add Index to create the index.

Adding Indexes Using the CLI

About this task

The following is the basic command for adding a secondary index on a JSON table. 
maprcli table index add 
  -path <path>
  -index <index name>
  -indexedfields < indexed field names >

See table index add for a description of the complete syntax.