Loading JSON Documents into a HPE Ezmeral Data Fabric Database Table with dbshell Commands

You can use the INSERT command in the mapr dbshell to load JSON documents into a HPE Ezmeral Data Fabric Database table.

The INSERT command is useful when inserting a small number of JSON documents into a JSON table.
NOTE
Alternatively, you can put JSON documents in a flat text file and use the mapr importJSON command to import the JSON documents into a table. The mapr importJSON command is useful when you need to insert many documents into a table. Refer to HPE Ezmeral Data Fabric Database JSON ImportJSON for instruction.
NOTE
The examples in this document use the student data in theQuerying HBase Tutorial to recreate the binary “students” table as a JSON table in HPE Ezmeral Data Fabric Database.
To load JSON documents into a HPE Ezmeral Data Fabric Database table through the mapr dbshell, complete the following steps:
  1. Run the following command to start the mapr dbshell:
    mapr dbshell
  2. Run the following command to create a table:
    create <table-name>

Example:
 create students
    NOTE
    By default, the table is stored in the default directory. The default directory is the current directory on the Data Fabric file system, which is set to the user’s home directory when the mapr dbshell starts. Include a file path, as shown, if you do not want the table stored in the default directory:
    create /file/path/table-name
  3. Load the JSON documents into the table using the INSERT command, as shown:
    insert <table-name> --value '{JSON-document}'

Example:
insert students --value '{"_id":"student1", "name":"Alice", "street":"123 Ballmer Av", "zipcode":12345, "state":"CA"}'
insert students --value '{"_id":"student2", "name":"Bob", "street":"1 Infinite Loop", "zipcode":12345, "state":"CA"}'
insert students --value '{"_id":"student3", "name":"Frank", "street":"435 Walker Ct", "zipcode":12345, "state":"CA"}'
insert students --value '{"_id":"student4", "name":"Mary", "street":"56 Southern Pkwy", "zipcode":12345, "state":"CA"}'
  4. Run the following command to verify that the table was created:
    find <table-name>

Example:
find students
  5. Run the following command to close the mapr dbshell:
    exit
  6. If you need to start or restart Drill, run the following command:
    maprcli node services -name drill-bits -action start|restart -nodes <space-separated-list-of-drill-hostnames>
  7. Run the following command to start the Drill shell (SQLLine):
    sqlline
You can query the HPE Ezmeral Data Fabric Database JSON table from the Drill shell. If you did not include a file path when you created the table, the table was created in the current directory on the Data Fabric File system, which is set to the user’s home directory. For example, the following query specifies the default directory if the root user created the table without indicating a file path:
SELECT * FROM dfs.`/user/root/table-name`;

Example:
SELECT * FROM dfs.`/user/root/students`;
If the user created the table in a specific directory, the query must include the directory in which the table was created, as shown:
SELECT * FROM dfs.`/file/path/table-name`;