Need basic SQL documenation

[hickey]

I have just recently learned of marketstore and I am becoming convinced that it will be a better solution than using HDF5 files like I planned on.

Bringing up an instance in a docker container was pretty straight forward and works well. I configured the instance to pull pricing data for BTC, ETH and LTC from GDAX to give me some basic data to play with and understand how to work with the instance.

Connecting to the instance is not a problem. Where problems develop is trying to understand the flavor of SQL that is implemented. I can not find a way to see what symbols there is data for. I have tried to just playing around and the "help" for the SQL parser are utterly useless. Here is the output for the most useful information I have been able to discover:

» show BTC-USD/1D/Tick
line 1:0 mismatched input 'show' expecting {'(', SELECT, WITH, VALUES, TABLE, INSERT, EXPLAIN}
*parser.StatementContext
Syntax Error[1:0]: mismatched input 'show' expecting {'(', SELECT, WITH, VALUES, TABLE, INSERT, EXPLAIN}
Syntax Error[1:0]: mismatched input 'show' expecting {'(', SELECT, WITH, VALUES, TABLE, INSERT, EXPLAIN}
» select * from BTC;
Directory path /data/BTC/1970.bin not found in catalog
» select * from BTC-USD;
line 1:17 mismatched input '-' expecting ';'
Syntax Error[1:17]: mismatched input '-' expecting ';'
Syntax Error[1:17]: mismatched input '-' expecting ';'
» select * from BTC-USD/1D;
line 1:17 mismatched input '-' expecting ';'
Syntax Error[1:17]: mismatched input '-' expecting ';'
Syntax Error[1:17]: mismatched input '-' expecting ';'
» select * from 'BTC-USD'/1D;
line 1:14 mismatched input ''BTC-USD'' expecting {'(', ADD, ALL, SOME, ANY, AT, NO, SUBSTRING, POSITION, TINYINT, SMALLINT, INTEGER, DATE, TIME, TIMESTAMP, INTERVAL, YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, ZONE, FILTER, OVER, PARTITION, RANGE, ROWS, PRECEDING, FOLLOWING, CURRENT, ROW, SCHEMA, COMMENT, VIEW, REPLACE, GRANT, REVOKE, PRIVILEGES, PUBLIC, OPTION, EXPLAIN, ANALYZE, FORMAT, TYPE, TEXT, GRAPHVIZ, LOGICAL, DISTRIBUTED, VALIDATE, SHOW, TABLES, SCHEMAS, CATALOGS, COLUMNS, COLUMN, USE, PARTITIONS, FUNCTIONS, TO, SYSTEM, BERNOULLI, POISSONIZED, TABLESAMPLE, UNNEST, ARRAY, MAP, SET, RESET, SESSION, DATA, START, TRANSACTION, COMMIT, ROLLBACK, WORK, ISOLATION, LEVEL, SERIALIZABLE, REPEATABLE, COMMITTED, UNCOMMITTED, READ, WRITE, ONLY, CALL, INPUT, OUTPUT, CASCADE, RESTRICT, INCLUDING, EXCLUDING, PROPERTIES, NFD, NFC, NFKD, NFKC, IF, NULLIF, COALESCE, IDENTIFIER, DIGIT_IDENTIFIER, QUOTED_IDENTIFIER, BACKQUOTED_IDENTIFIER}
Syntax Error[1:14]: mismatched input ''BTC-USD'' expecting {'(', ADD, ALL, SOME, ANY, AT, NO, SUBSTRING, POSITION, TINYINT, SMALLINT, INTEGER, DATE, TIME, TIMESTAMP, INTERVAL, YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, ZONE, FILTER, OVER, PARTITION, RANGE, ROWS, PRECEDING, FOLLOWING, CURRENT, ROW, SCHEMA, COMMENT, VIEW, REPLACE, GRANT, REVOKE, PRIVILEGES, PUBLIC, OPTION, EXPLAIN, ANALYZE, FORMAT, TYPE, TEXT, GRAPHVIZ, LOGICAL, DISTRIBUTED, VALIDATE, SHOW, TABLES, SCHEMAS, CATALOGS, COLUMNS, COLUMN, USE, PARTITIONS, FUNCTIONS, TO, SYSTEM, BERNOULLI, POISSONIZED, TABLESAMPLE, UNNEST, ARRAY, MAP, SET, RESET, SESSION, DATA, START, TRANSACTION, COMMIT, ROLLBACK, WORK, ISOLATION, LEVEL, SERIALIZABLE, REPEATABLE, COMMITTED, UNCOMMITTED, READ, WRITE, ONLY, CALL, INPUT, OUTPUT, CASCADE, RESTRICT, INCLUDING, EXCLUDING, PROPERTIES, NFD, NFC, NFKD, NFKC, IF, NULLIF, COALESCE, IDENTIFIER, DIGIT_IDENTIFIER, QUOTED_IDENTIFIER, BACKQUOTED_IDENTIFIER}
Syntax Error[1:14]: mismatched input ''BTC-USD'' expecting {'(', ADD, ALL, SOME, ANY, AT, NO, SUBSTRING, POSITION, TINYINT, SMALLINT, INTEGER, DATE, TIME, TIMESTAMP, INTERVAL, YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, ZONE, FILTER, OVER, PARTITION, RANGE, ROWS, PRECEDING, FOLLOWING, CURRENT, ROW, SCHEMA, COMMENT, VIEW, REPLACE, GRANT, REVOKE, PRIVILEGES, PUBLIC, OPTION, EXPLAIN, ANALYZE, FORMAT, TYPE, TEXT, GRAPHVIZ, LOGICAL, DISTRIBUTED, VALIDATE, SHOW, TABLES, SCHEMAS, CATALOGS, COLUMNS, COLUMN, USE, PARTITIONS, FUNCTIONS, TO, SYSTEM, BERNOULLI, POISSONIZED, TABLESAMPLE, UNNEST, ARRAY, MAP, SET, RESET, SESSION, DATA, START, TRANSACTION, COMMIT, ROLLBACK, WORK, ISOLATION, LEVEL, SERIALIZABLE, REPEATABLE, COMMITTED, UNCOMMITTED, READ, WRITE, ONLY, CALL, INPUT, OUTPUT, CASCADE, RESTRICT, INCLUDING, EXCLUDING, PROPERTIES, NFD, NFC, NFKD, NFKC, IF, NULLIF, COALESCE, IDENTIFIER, DIGIT_IDENTIFIER, QUOTED_IDENTIFIER, BACKQUOTED_IDENTIFIER}
» select * from `BTC-USD`/1D;
line 1:23 mismatched input '/' expecting ';'
Syntax Error[1:23]: mismatched input '/' expecting ';'
Syntax Error[1:23]: mismatched input '/' expecting ';'
» select * from `BTC-USD/1D`;
Directory path /data/BTC-USD/1D/1970.bin not found in catalog
» select * from `BTC-USD/1D/2016`;
Directory path /data/BTC-USD/1D/2016/1970.bin not found in catalog

Now if one knows how the data and tables are structured, the above probably makes a good amount of sense, but for someone without this knowledge this become an exercise in frustration.

Please be kind to all of us starting out trying to use your software and give us some sample commands. It does not have to be formal documentation. I would be thrilled if you just had a cheatsheet that listed the most common commands and showed some examples.

Heck, I would be happy to write something up and generate a PR if I could just get anything in your software to work.

[hickey]

Well, I have to say that I am a little disappointed. It has been effectively 2 weeks now and no activity. I would think that this GitHub project was abandoned, but I see recent updates. So I have to conclude that there is no desire to build a community around marketstore. I would have been happy if there was just a comment here that gave a couple of the basic SQL commands for experimentation. I could have taken that and build out a document for any other new comer that is having issues.

I did have a little bit of success recently using the show command, but even problems there. It was easy enough to enable the GDAX plugin. But the data gets written as /data/gdax_<SYMBOL> and everything indicates that it should be queried with the symbol I found that I had to query as gdax_. Not a great user experience.

Well, I am running away and going back to HDF5 files. At least there I am able to find the data in a structured format and use it vs. having to have to fight to find the data and get ignored.

If you are finding what ever community you have for marketstore shrinking, you may want to re-read this issue a few more times to learn that people are not going to use a software--no matter how wonderful it may be--without even a modicum of documentation to answer their questions.

[Bexanderthebex]

@hickey I am monitoring marketstore as well and I think I like the project. I think I will take this issue to address your concerns above as part of their hacktoberfest event. I will do some code reading and create a documentation that addresses your concerns. I however will need your feedback with it so I will have to ask you to review the PR that I will make. Sounds good?

[hickey]

@Bexanderthebex sure. A few things that I have seen indicates that marketstore has an SQL engine like PostgreSQL. I saw somewhere that Alpaca Markets hired one of the people behind PostgreSQL to help build marketstore. From my playing with it the SQL command line I found that it had a couple of similar command structures, but no where near the full implementation that would help with discovering how everything was structured.

[Bexanderthebex]

@hickey Just to update you with my progress. Once you load the cli client, you should load the data that you backfilled or streamed first by running \show <data_location> whereas the default data location is within the project folder ./data; After that, you will be able to query the data using the compliant SQL select queries. I'm still doing some code reading and testing to make sure what I know is correct before opening a PR for the documentation

[christrt9]

Does anyone find some documentation for marketstore cli ? i Cannot search or show anything

[travispulley]

Not ideal, but I was able to overcome the non-documentation a few times by using gh copilot. Code it as close as you can, lots of context and such, then make a comment about what you're trying to do as a prompt, and that might reveal the syntax you're looking for.
I used that to make a function that normalizes my data for compatibility with marketstore, saved me a lot of time!