Adding Secondary Indexes on JSON Tables

Describes how to add secondary indexes on MapR Database JSON tables.

You can add secondary indexes using the MapR Control System (MCS) 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 MapR 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. MapR 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.

Adding Indexes Using the MapR Control System

  1. Log in to MCS 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 dropdown menu.
    3. Select the function of the field from the Function dropdown 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

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.