Where is the mapdql documented?


#1

Questions:

  1. Where is the mapdql documented? I have read through https://www.mapd.com/docs/latest/mapd-core-guide/mapdql but haven’t been able to successfully create my table.
  2. What are the table and column name enclosures? In MySQL I could use ` for example.
  3. What rules are there regarding the names that can be used?
  4. How do you troubleshoot syntax errors? I ran into an error “Non-query expression encountered in illegal context” but it doesn’t say where the error occurred. The SQL statement is over 400 lines and I’ve searched through and removed or fixed anything I thought might possibly be an issue. It could be the most minor character, but I might never find it.

What I have tried:

  1. So far I’ve removed any column name enclosure and replaced all spaces with an underscore.
  2. I replaced the word state with state1 in case state was a reserved word.
  3. I replaced all dashes and slashes with underscores.

Following the tutorial I can’t even insert the sample data that comes with the install. When I hit “1” to download and insert the flight data it downloads and unzips the data then says that the file can’t be found when it tries to insert it into the database. How is that possible? MapD chose the download and unzip locations automatically and my only input was to choose data sample 1. Why wouldn’t it be able to insert it’s own sample data?

I’m very new to Mapd so any help or direction would be really appreciated.


#2

Hi,

mapdql is one of the client interfaces you can use to create objects on database like databases,user,tables and so on, run queries, sql batches, load/unload data with copy command, check memory consumtion on system and gpu and so on.

the docs that explain how to create objects,which datatypes are supported, load and query the data are located here
https://www.mapd.com/docs/latest/mapd-core-guide/data-definition/
https://www.mapd.com/docs/latest/mapd-core-guide/import-export/
https://www.mapd.com/docs/latest/mapd-core-guide/dml/

if you are unfamiliar with datatypes supported and syntax of mapd you have two way to start

  1. you can create a csv file with column names and a sample of data (1k,2k rows) and import it with immerse tool (http://machine_name_or_ip_you_have_installed_mapd:8443), select new dasboard then import data, and select the csv you created on your local machine
    https://www.mapd.com/docs/latest/immerse-user-guide/importing-data/
  2. you can use the sql importer thru the mysql jdbc driver (i personally never used it)

Here is a link with various tutorials about mapd’s loading tools
https://www.mapd.com/docs/latest/getting-started/tutorials/#loading-data

after the creation of tables you can extract the DDL creation using the \d command of mapdql tool
\d table_name

hopes this helps


#3

@aznable. Thank you for answering me. I had already read and re-read every single link you gave. There is no database, table, or column name enclosure given in any of the documentation. I thought I still need to create the table before the other options would work for importing the data.

In order to use the ‘COPY FROM’ command, don’t you already have to have a table to copy into?

Regarding SQL importer, I thought it also needs a mapd table to copy into that would go in “-t [MapD table name]”. Please tell me if I’m mistaken. I haven’t been able to make a table and I haven’t been able to create a table by substituting a new table name thinking that mapd would somehow create the table. I have gotten errors at every step.

I’m running MapD in CPU only mode and don’t have a graphics card so Immerse doesn’t work.

EDIT:
I managed to test the Mapdql by deliberately placing invalid syntax. If my intentional syntax error was called then I knew the problem was further down the file. After a couple hours of testing in this way I was able to create a table and am presently loading a csv into that table.


#4

yes i don’t know if there is an enclosure for table or column names, sorry.

the copy from has to be read like…copy into table from file :slight_smile:

e.g.
mapdql> copy b_dma_fspe_spese_el from ‘/opt/opendata/b_dma_fspe_spese_el*’ with (delimiter=’|’);
Loaded: 455616372 recs, Rejected: 0 recs in 365.560000 secs
1 rows returned.
Execution time: 365564 ms, Total time: 365574 ms

is useful expecially if you have to load more files because it’s support wildcards, it’s quite fast and it’s performed on CPU only

i haven’t used sql importer yet but in docs is clearly written that’s able to create the target table if doesnt exists

SQL Importer executes a select statement on another database via JDBC and brings the result set into MapD Core.
If the table doesn’t exist it will create the table in MapD Core.

Which errors are you getting?

AFAIK Immerse works in CPU only mode, but you will miss the Backend Rendering feature, so you won’t be able to use all objects


#5

@aznable. Thank you.

For posterity:

  1. Mapdql is not fully documented but the speed is worth the trial and error. The documentation that does exists is in aznable’s response.

  2. Presently there are no table enclosures for Mapdql.

  3. While there are probably more rules, I have found that since there are no table or column name enclosures that table names can only have a-zA-Z0-9_. There are probably other characters you could use, but my create table query would not work until all the other characters were removed.

  4. You can insert your own error to see if the problem is before or after that break point. The raw code for the parser file can be seen at
    https://github.com/mapd/mapd-core/blob/master/Parser/ParserNode.cpp and if you read through you can see the exceptions that may be thrown.
    Some of the errors I ran in to were:
    CHAR and VARCHAR must have a positive dimension. Mapdql does not allow char(0), and if you ever used it for booleans in MySQL, believe me, you won’t need it anymore.
    DECIMAL and NUMERIC precision cannot be larger than 19.

I have also confirmed that "copy <table> from <file>" requires the table already be created. I was able to achieve loading a table as described in the edit above. I never could get the JDBC working because I don’t know how to find the driver names and version and get all the syntax correct.

The speed, even with CPU is phenomenal. It makes all the hours of figuring it out very well worthwhile. I can hardly believe how fast this is on a cpu based system.

A 100 GB file took 20 minutes to load. The table has over 400 columns and a simple count query against 5 or 6 unindexed columns would take about 2-3 minutes in MySQL. In MapD the result comes back in a fraction of a second.

Queries that used to take several minutes to execute now execute in less than a second even though I have no GPU in my system.

I haven’t been able to get immerse to work yet, but I’ll keep on it.


#6

Hi,

Sorry fhat you have had an awkward experience. Glad to see you persevered and saw the benefits.

Could you detail your issue with accessing immerse? Did you read the post here?

Details for JDBC are here. What client are you trying to connect with?

Regards


#7

And you would get a boost even if you where comparing with well established in-memory database like oracle with in-memory or exasol.

with a quad core desktop mapd surpasses the performance of an recent exadata (2 database machine) using 32 cores by 2 times in cpu mode and by 13 times on GPU mode


#8

@dwayneberry. Thank you for replying. I hadn’t read your other immerse post until you linked here. My issue was that the firewall on the gateway was blocking requests from the LAN. I opened it up and have it working now. It’s very neat.

Regarding the client, my eventual goal is to be able to run analytics from a web platform where I can send an sql query and get back an answer, so if there existed a node.js or php client I would be all over it.

Obviously, you are much more familiar with Mapd. How would you go about interfacing with Mapd for a web application?

What I’ve tried so far:
The ability to run mapdql from the commandline without opening the interface would be more than acceptable because I could shell_exec, but that does not seem to be supported.

I’m able to open an apache thrift connection to the server on the correct port, but am not able to run any Mapd commands. In the python, C# and java examples that are included in the Sample code folder there is the line from mapd import MapD or using static mapd. Where is the file that’s being included there? I’ve read through the interface definitions on mapd.thrift, but it’s written for java and I don’t know what to do with it.


#9

Hi

You should look at the GitHub projects Mapd-connect and Mapd-charting

Hopefully these will give you much of what you need

Regards


#10

Graphistry has also built reactive node bindings to MapD Core that you might want to take a look at.


#11

Thank you for pointing me towards Mapd-connect, Mapd-charting and reactive node bindings to MapD Core.