Loading TOC...
SQL Data Modeling Guide (PDF)

SQL Data Modeling Guide — Chapter 2

SQL on MarkLogic Server Quick Start

This chapter describes how to set up your MarkLogic Server for SQL. This chapter describes how to set up a typical development environment in which the SQL client and MarkLogic Server are configured on the same machine. For a production environment, you would typically configure your SQL client and MarkLogic Server on separate machines.

You must have the admin role on MarkLogic Server to complete the procedures described in this chapter.

The main topics in this chapter are:

Setup MarkLogic Server

Install MarkLogic Server on the database server, as described in the Installation Guide. and follow these procedures:

Create a Schema Database and a SQL Database

How to create a database is described in detail in Creating a New Database in the Administrator's Guide. This section provides a quick-start procedure for creating the database used in this example.

Every SQL database must have its own separate schema database.

  1. Open your browser and navigate to the Admin Interface:
    http://hostname:8001

    Where hostname is the name of your MarkLogic Server host machine.

  2. Click the Forests icon in the left tree menu.
  3. Click the Create tab at the top right. The Create Forest page displays. Enter ‘SQLschemas' as the name of your forest in the Forest Name textbox. Click OK.

  1. Click the Create tab at the top right. The Create Forest page displays. Enter ‘SQLdata' as the name of your forest in the Forest Name textbox. Click OK.

  1. Click the Databases icon in the left tree menu.
  2. Click the Create tab at the top right. The Create Database page displays. Enter ‘SQLschemas' as the name of the new database and click Ok:

  3. At the top of the page click Database->Forests

  4. Check the SQLschemas box to attach the SQLschemas forest. Click Ok:

  5. Click the Create tab at the top right. The Create Database page displays. Enter ‘SQLdata' as the name of the new database and select ‘SQLschemas' as the Schema Database.

  6. Scroll down the Create Database page to the Triple Index setting and click ‘true' to enable triple indexing. Click Ok:

  7. At the top of the page click Database->Forests

  8. Check the SQLdata box to attach the SQLdata forest. Click Ok:

Create an ODBC App Server

Schemas and views represent content stored in a MarkLogic Server database. Each content database used by a SQL client is managed by an ODBC App Server that accepts SQL queries from the SQL client and responds by returning MarkLogic Server data in tuple form. An ODBC App Server can manage only one content database. However, a single content database can be managed by multiple ODBC App Servers.

ODBC App Servers are described in detail in the ODBC Servers chapter in the Administrator's Guide.

Open the Admin Interface

To create a new server, complete the following steps:

  1. Click the Groups icon in the left tree menu.
  2. Click the group in which you want to define the ODBC server (for example, Default).
  3. Click the App Servers icon on the left tree menu.
  4. Click the Create ODBC tab at the top right. The Create ODBC Server page will display:

  5. In the Server Name field, enter a shorthand name for this ODBC server. In this example, the name of the App Server is ‘SQL.'
  6. In the Root directory field, enter /.
  7. In the Port field, enter the port number through which you want to make this ODBC server available. The default PostgreSQL listening socket port is 5432.
  8. Leave the Modules field as (file system).
  9. In the Database field, select the ‘SQLdata' database you created in Create a Schema Database and a SQL Database.

Load the Data

This section describes the procedure for loading the sample documents.

  1. Go to the following URL to open Query Console:
    http://hostname:8000/qconsole/

    Where hostname is the name of your MarkLogic Server host.

  2. Select the SQLdata database from the Content Source pulldown menu and JavaScript from the Query Type menu.

  1. Cut and paste the following XQuery into Query Console:
    declareUpdate();
    xdmp.documentInsert(
       "/employee1.json",
        { "Employee": {
          "ID": 1,
          "FirstName": "John",
          "LastName": "Widget",
          "Position": "Manager of Human Resources" }}),
    xdmp.documentInsert(
       "/employee2.json",
        { "Employee": {
          "ID": 2,
          "FirstName": "Jane",
          "LastName": "Lead",
          "Position": "Manager of Widget Research" }}),
    xdmp.documentInsert(
       "/employee3.json",
        { "Employee": {
          "ID": 3,
          "FirstName": "Steve",
          "LastName": "Manager",
          "Position": "Senior Technical Lead" }}),
    xdmp.documentInsert(
       "/employee4.json",
        { "Employee": {
          "ID": 4,
          "FirstName": "Debbie",
          "LastName": "Goodall",
          "Position": "Senior Widget Researcher" }}),
    xdmp.documentInsert(
       "/employee5.json",
        { "Employee": {
          "ID": 14,
          "FirstName": "Lori",
          "LastName": "Baker",
          "Position": "Senior Wingnut" }}),
    xdmp.documentInsert(
       "/employee6.json",
        { "Employee": {
          "ID": 15,
          "FirstName": "Steve",
          "LastName": "Lostit",
          "Position": "Mad Scientist" }}),
    xdmp.documentInsert(
       "/employee7.json",
        { "Employee": {
          "ID": 16,
          "FirstName": "Donald",
          "LastName": "Putin",
          "Position": "Power Couple" }}),
    xdmp.documentInsert(
       "/expense1.json",
        { "Expenses": {
             "EmployeeID": 1,
             "Date": "2012-06-27",
             "Amount": 131.02,
             "Purchase": {
                "Category": "Lodging",
                "Vendor": "Hyatt Hotels",
                "Description": "Exec. King Room"}}}),
     xdmp.documentInsert(
       "/expense2.json",
        { "Expenses": {
             "EmployeeID": 2,
             "Date": "2012-06-27",
             "Amount": 155.22,
             "Purchase": {
                "Category": "Transportation",
                "Vendor": "Alaska",
                "Description": "SFO > SEA"}}}),
    xdmp.documentInsert(
       "/expense3.json",
        { "Expenses": {
             "EmployeeID": 1,
             "Date": "2012-08-03",
             "Amount": 59.95,
             "Purchase": {
                "Category": "Meals",
                "Vendor": "Doug's Dinner",
                "Description": "Dinner"}}}),
    xdmp.documentInsert(
       "/expense4.json",
        { "Expenses": {
             "EmployeeID": 3,
             "Date": "2012-05-07",
             "Amount": 162.95,
             "Purchase": {
                "Category": "Lodging",
                "Vendor": "Hilton Hotels",
                "Description": "Exec. Suite"}}}),
    xdmp.documentInsert(
       "/expense5.json",
        { "Expenses": {
             "EmployeeID": 3,
             "Date": "2012-05-30",
             "Amount": 120.00,
             "Purchase": {
                "Category": "Lodging",
                "Vendor": "Kingsman Motel",
                "Description": "Reg Room"}}}),
    xdmp.documentInsert(
       "/expense6.json",
        { "Expenses": {
             "EmployeeID": 4,
             "Date": "2012-03-23",
             "Amount": 155.55,
             "Purchase": {
                "Category": "Lodging",
                "Vendor": "Waterfront Hotel",
                "Description": "Queen Room"}}}),
    xdmp.documentInsert(
       "/expense7.json",
        { "Expenses": {
             "EmployeeID": 4,
             "Date": "2012-06-05",
             "Amount": 104.29,
             "Purchase": {
                "Category": "Meals",
                "Vendor": "Good Eats",
                "Description": "Client Lunch"}}}),
    xdmp.documentInsert(
       "/GoodEats.json",
        { "ApprovedVendor": {
             "Name": "Good Eats",
             "Address": {
                "Street": "707 Oxford Rd.",
                "City": "Ann Arbor",
                "Region": "MI",
                "PostalCode": "48104",
                "PostalCode": "USA",
                "Phone": "(313) 555-5735"}}}),
    xdmp.documentInsert(
       "/WaterfrontHotel.json",
        { "ApprovedVendor": {
             "Name": "Waterfront Hotel",
             "Address": {
                "Street": "1000 Coast Rd.",
                "City": "Santa Cruz",
                "Region": "CA",
                "PostalCode": "94330",
                "PostalCode": "USA",
                "Phone": "(831) 745-8913"}}}),
    xdmp.documentInsert(
       "/KingsmanMotel.json",
        { "ApprovedVendor": {
             "Name": "Kingsman Motel",
             "Address": {
                "Street": "4832 Frankster St.",
                "City": "Renor",
                "Region": "NV",
                "PostalCode": "88660",
                "PostalCode": "USA",
                "Phone": "(702) 436-3785"}}}),
    xdmp.documentInsert(
       "/Hilton.json",
        { "ApprovedVendor": {
             "Name": "Hilton Hotels",
             "Address": {
                "Street": "555 Market St.",
                "City": "San Francisco",
                "Region": "CA",
                "PostalCode": "94033",
                "PostalCode": "USA",
                "Phone": "(415) 540-8732"}}}),
    xdmp.documentInsert(
       "/Hyatt.json",
        { "ApprovedVendor": {
             "Name": "Hyatt Hotels",
             "Address": {
                "Street": "9023 Caterberry Ave.",
                "City": "Seattle",
                "Region": "WA",
                "PostalCode": "56445",
                "PostalCode": "USA",
                "Phone": "(206) 321-3152"}}}),
    xdmp.documentInsert(
       "/MealLimit.json",
        { "ExpenseLimit": {
             "Category": "Meals",
             "Limit": 100}}),
    xdmp.documentInsert(
       "/LodgingLimit.json",
        { "ExpenseLimit": {
             "Category": "Lodging",
             "Limit": 300}}),
    xdmp.documentInsert(
       "/TransLimit.json",
        { "ExpenseLimit": {
             "Category": "Transportation",
             "Limit": 200}})
  2. In the control bar below the query window, click Run:

Create Template Views

This section describes how to use the XQuery API to create the template views used by SQL queries.

  1. Create a template view in the main schema, named employees. Specify the Employee element as the context and columns for EmployeeID, FirstName, and LastName. Use tde:template-insert to insert the template document into the SQLschemas database as /employees.xml.
    xquery version "1.0-ml"; 
    
    import module namespace tde = "http://marklogic.com/xdmp/tde" 
           at "/MarkLogic/tde.xqy";
    
    let $employees :=
    <template xmlns="http://marklogic.com/xdmp/tde">
      <context>/Employee</context>
      <rows>
        <row>
          <schema-name>main</schema-name>
          <view-name>employees</view-name>
          <columns>
            <column>
              <name>EmployeeID</name>
              <scalar-type>int</scalar-type>
              <val>ID</val>
            </column>
            <column>
              <name>FirstName</name>
              <scalar-type>string</scalar-type>
              <val>FirstName</val>
            </column>
            <column>
              <name>LastName</name>
              <scalar-type>string</scalar-type>
              <val>LastName</val>
            </column>
            <column>
              <name>Position</name>
              <scalar-type>string</scalar-type>
              <val>Position</val>
            </column>
           </columns>
        </row>
      </rows>
    </template>
    
    return tde:template-insert("/employees.xml", $employees)
  2. Create a second view in the main schema, named expenses, with a scope on the Expenses element as the context and columns for EmployeeID, Date, and Amount. Use tde:template-insert to insert the template document into the SQLschemas database as /expenses.xml.
    xquery version "1.0-ml"; 
    
    import module namespace tde = "http://marklogic.com/xdmp/tde" 
           at "/MarkLogic/tde.xqy";
    
    let $expenses :=
    <template xmlns="http://marklogic.com/xdmp/tde">
      <context>/Expenses</context>
      <rows>
        <row>
          <schema-name>main</schema-name>
          <view-name>expenses</view-name>
          <columns>
            <column>
              <name>EmployeeID</name>
              <scalar-type>int</scalar-type>
              <val>EmployeeID</val>
            </column>
            <column>
              <name>Date</name>
              <scalar-type>date</scalar-type>
              <val>Date</val>
            </column>
            <column>
              <name>Category</name>
              <scalar-type>string</scalar-type>
              <val>Purchase/Category</val>
            </column>
            <column>
              <name>Vendor</name>
              <scalar-type>string</scalar-type>
              <val>Purchase/Vendor</val>
            </column>
            <column>
              <name>Amount</name>
              <scalar-type>decimal</scalar-type>
              <val>Amount</val>
            </column>
           </columns>
        </row>
      </rows>
    </template> 
    
    return tde:template-insert("/expenses.xml", $expenses)
  3. Create a two more views in the main schema, named approvedvendor and expenselimit as follows.
    xquery version "1.0-ml"; 
    
    import module namespace tde = "http://marklogic.com/xdmp/tde" 
           at "/MarkLogic/tde.xqy";
    
    let $vendors :=
    <template xmlns="http://marklogic.com/xdmp/tde">
      <context>ApprovedVendor</context>
      <rows>
        <row>
          <schema-name>main</schema-name>
          <view-name>approvedvendor</view-name>
          <columns>
            <column>
              <name>Vendor</name>
              <scalar-type>string</scalar-type>
              <val>Name</val>
            </column>
            <column>
              <name>City</name>
              <scalar-type>string</scalar-type>
              <val>Address/City</val>
            </column>
           </columns>
        </row>
      </rows>
    </template> 
    
    return tde:template-insert("/vendors.xml", $vendors);
    xquery version "1.0-ml"; 
    
    import module namespace tde = "http://marklogic.com/xdmp/tde" 
           at "/MarkLogic/tde.xqy";
    
    let $limits :=
    <template xmlns="http://marklogic.com/xdmp/tde">
      <context>ExpenseLimit</context>
      <rows>
        <row>
          <schema-name>main</schema-name>
          <view-name>expenselimit</view-name>
          <columns>
            <column>
              <name>Category</name>
              <scalar-type>string</scalar-type>
              <val>Category</val>
            </column>
            <column>
              <name>Limit</name>
              <scalar-type>decimal</scalar-type>
              <val>Limit</val>
            </column>
           </columns>
        </row>
      </rows>
    </template> 
    
    return tde:template-insert("/limits.xml", $limits) 
  4. List the views that you just created.
    tde:get-view("main","employees"),
    tde:get-view("main","expenses"),
    tde:get-view("main","approvedvendor"),
    tde:get-view("main","expenselimit")

    If you change a template view, you must reindex your content database.

Enter SQL Queries to Test

  1. To test that everything is working correctly, click + to open another query window:

  2. In the new query window, make sure you have ‘SQLdata' selected in the Content Source pull-down menu. Select a Query Type of SQL:

Enter the following query:

select * from employees
  1. In the control bar below the query window, select Run.
  2. You should see results that look like the following:

MarkLogic Server treats SQL as case insensitive. Uppercase and lowercase characters are treated the same.

Using MLSQL

The MLSQL tool is a command line interface for issuing SQL statements. The executable MLSQL file is located in a MarkLogic installation at the following location:

  • Windows: c:\Program Files\MarkLogic\mlsql\mlsql.exe
  • Linux/Unix: /opt/MarkLogic/bin/mlsql

    On Linux/Unix, MLSQL is installed along with the ODBC driver, as described in Configuring the ODBC Driver on Linux.

    MLSQL is not supported on Mac OS.

You must be assigned the sql-execution role on MarkLogic Server to use MLSQL.

To use the MLSQL tool, open a shell window and enter:

mlsql -h hostname -p 5432 -U username

Enter your password when you see a prompt like:

username=>

Enter a few SQL queries, like the following:

username=> SELECT * FROM main.employees;
username=> SELECT employees.FirstName, employees.LastName,
SUM(expenses.Amount) AS ExpensesPerEmployee
FROM employees, expenses
WHERE employees.EmployeeID = expenses.EmployeeID
GROUP BY employees.FirstName, employees.LastName;
username=> SELECT employees.FirstName, employees.LastName,
SUM(expenses.Amount) AS ExpensesPerEmployee
FROM employees JOIN expenses
ON employees.EmployeeID = expenses.EmployeeID
GROUP BY employees.FirstName, employees.LastName 
ORDER BY ExpensesPerEmployee;

A semicolon (;) is used in MLSQL to designate the end of a SQL query.

To demonstrate the purpose of the Searchable Field in the view, try the following queries:

username=> SELECT * from employees WHERE employees MATCH "Manager";
username=> SELECT * from employees WHERE employees 
MATCH "position:Manager";

The first query searches for the word 'Manager' in all of the document elements. The position:Manager specification in the second query narrows the search for 'Manager' to the elements included in the position field, which in this case is the Position element.

To exit MLSQL, enter: \q

If you get results from the SQL queries, you can proceed to connecting your BI tool to MarkLogic Server, as described in Connecting Qlik to MarkLogic Server and Connecting Tableau to MarkLogic Server.

If you add or change the contents of a view, database, or documents, you must exit and restart MLSQL.

The MLSQL session commands are:

CommandDescription
\copyrightReturns distribution terms.
\hReturns list of available SQL commands.
\?Returns list of available psql commands.
\g Re-executes the last query.
\qExits MLSQL.

The syntax of a MLSQL command is:

  mlsql [OPTION]... [DBNAME [USERNAME]]

Where:

Connection options:

OptionDescription
-h, --host=HOSTNAMEDatabase server host or socket directory (default: "local socket")
-p, --port=PORTODBC server port (default: "5432")
-U, --username=USERNAMEDatabase user name (default: "username")
-w, --no-passwordNever prompt for password
-W, --password Force password prompt (should happen automatically)

General options:

OptionDescription
-c, --command=COMMANDRun only single command (SQL or internal) and exit
-d, --dbname=DBNAMEDatabase name to connect to (default: "gfurbush")
-f, --file=FILENAMEExecute commands from file, then exit
-l, --listList available databases, then exit
-v, --set=, --variable=NAME=VALUESet psql variable NAME to VALUE
-X, --no-psqlrcDo not read startup file (~/.psqlrc)
-1 ("one"), --single-transactionExecute command file as a single transaction
--helpShow help, then exit
--versionOutput version information, then exit

Input and output options:

OptionDescription
-a, --echo-allEcho all input from script
-e, --echo-queriesEcho commands sent to server
-E, --echo-hiddenDisplay queries that internal commands generate
-L, --log-file=FILENAMESend session log to file
-n, --no-readlineDisable enhanced command line editing (readline)
-o, --output=FILENAMESend query results to file (or |pipe)
-q, --quietRun quietly (no messages, only query output)
-s, --single-stepSingle-step mode (confirm each query)
-S, --single-lineSingle-line mode (end of line terminates SQL command)

Output format options:

OptionDescription
-A, --no-alignUnaligned table output mode
-F, --field-separator=STRINGSet field separator (default: "|")
-H, --htmlHTML table output mode
-P, --pset=VAR[=ARG]Set printing option VAR to ARG (see \pset command)
-R, --record-separator=STRINGSet record separator (default: newline)
-t, --tuples-onlyPrint rows only
-T, --table-attr=TEXTSet HTML table tag attributes (e.g., width, border)
-x, --expandedTurn on expanded table output

« Previous chapter
Next chapter »
Powered by MarkLogic Server 7.0-4.1 and rundmc | Terms of Use | Privacy Policy