Loading Data into a JSON Table from JSON Files

If you have data stored in JSON files that you want to store in a JSON table, create a new JSON table in MapR Database from the mapr dbshell and then load the data into the table using the insert command. Or, you can use the mapr importJSON command to create the table and import data. The insert command is useful when inserting a small number of JSON documents into a JSON table. The mapr importJSON command is useful when inserting many documents into a JSON table.

For example, to recreate the binary “students” table as a JSON table in MapR Database:
  1. Start the mapr dbshell:
    mapr dbshell
  2. Issue the create command to create the JSON table, including a filepath if you do not want the table stored in the default directory. The default directory is the current directory on MapR file system which is set to the user’s home directory when the mapr dbshell starts.
    create students
  3. View Create the HBase Table to see the data used to create the “students” binary table. The binary table was created using a series of put commands. To create a JSON table, the data needs to be in JSON format. Issue the following commands to insert each JSON record into the JSON table:
    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"}'

    Alternatively, you can create a flat text JSON file that contains the records and run the mapr importJSON command to import the records into the table from the file.

  4. Issue find <table_name> to verify that the table was created.
  5. Issue exit and press return to close the mapr dbshell.
Now you can start Drill and query the 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 MapR 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 “students” table without indicating a file path:
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:
SELECT * FROM dfs.`/file/path/students`;