stevearc / dql Goto Github PK

I'm a total newbie to your project, and I'm probably reading the docs wrong, but I keep getting exceptions when I try to save to a non-default file format:

us-east-1> SCAN pid, sponsor, subsection FROM eligible.persons WHERE NOT attribute_exists(provenance_id) SCAN LIMIT 100 SAVE wackamole;
Saved 23 records to wackamole
us-east-1> SCAN pid, sponsor, subsection FROM eligible.persons WHERE NOT attribute_exists(provenance_id) SCAN LIMIT 100 SAVE wackamole.json;
Traceback (most recent call last):
  File "/home/rcobb/.pyenv/versions/3.6.5/lib/python3.6/cmd.py", line 214, in onecmd
    func = getattr(self, 'do_' + cmd)
AttributeError: 'DQLClient' object has no attribute 'do_SCAN'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/rcobb/.pyenv/versions/3.6.5/lib/python3.6/site-packages/dql/cli.py", line 195, in start
    self.cmdloop()
  File "/home/rcobb/.pyenv/versions/3.6.5/lib/python3.6/cmd.py", line 138, in cmdloop
    stop = self.onecmd(line)
  File "/home/rcobb/.pyenv/versions/3.6.5/lib/python3.6/cmd.py", line 216, in onecmd
    return self.default(line)
  File "/home/rcobb/.pyenv/versions/3.6.5/lib/python3.6/site-packages/dql/cli.py", line 578, in default
    self._run_cmd(command)
  File "/home/rcobb/.pyenv/versions/3.6.5/lib/python3.6/site-packages/dql/cli.py", line 602, in _run_cmd
    results = self.engine.execute(command)
  File "/home/rcobb/.pyenv/versions/3.6.5/lib/python3.6/site-packages/dql/engine.py", line 972, in execute
    pretty_format)
  File "/home/rcobb/.pyenv/versions/3.6.5/lib/python3.6/site-packages/dql/engine.py", line 299, in execute
    result = self._run(statement)
  File "/home/rcobb/.pyenv/versions/3.6.5/lib/python3.6/site-packages/dql/engine.py", line 316, in _run
    return self._scan(tree)
  File "/home/rcobb/.pyenv/versions/3.6.5/lib/python3.6/site-packages/dql/engine.py", line 608, in _scan
    return self._select(tree, True)
  File "/home/rcobb/.pyenv/versions/3.6.5/lib/python3.6/site-packages/dql/engine.py", line 593, in _select
    ofile.write(self._encoder.encode(item))
TypeError: a bytes-like object is required, not 'str'
us-east-1> 

This happens with both .json and .csv. The values in the response columns are all UTF-8 (actually ASCII-7bit) strings or NULL. I'm using:

$ dql --version
0.5.26
$ python --version
Python 3.6.5

Is there any support for aws profile like passing profile with -p. A workaround is to set environment variable AWS_DEFAULT_PROFILE.

Hi there,

Sorry for asking a silly question but I need help since I couldn't find any clues in the documentation.

I want dql (command line mode) to connect to Dynamodb Local binding to localhost:8000.
( http://docs.aws.amazon.com/amazondynamodb/latest/developerguide/DynamoDBLocal.html )

How can I make it ?

Cheers

I was reading through the 0.5.0 documentation and noticed that the examples for scan had been removed. Saying: See SELECT. This is the exact same as a SELECT statement except it is always allowed to perform table scans. Then I ended up on the 0.4.0 docs and realized that there is a totally different way for querying using scan than querying using SELECT.

E.g. This won't work in SCAN:

SCAN *
FROM my-tablename
WHERE attribute = 'abc'

But this will:

SCAN *
FROM my-tablename
FILTER attribute = 'abc'

Can you please add back SCAN specific examples as some examples are actually different and knowing the differences would have saved me time and will potentially save others time as well.

Summary

ls on a table which has on-demand indexes fails.

> ls golie-on-demand-table
AttributeError: 'GlobalIndex' object has no attribute 'item_count'

DQL Details:

DQL Version: 0.5.29-dev0
Python: 3.7.7
OS: MacOS 10.15.6 (19G2021)

Table with indexes:

Trace:

Sorry to ask it as an issue but I couldn't find any other better channel to ask.. :)

I'd like to know what functions are available to be used in a select/scan statement. For example:

• How can I filter dates, let's say last three months or fixed values ?
• I could use WHERE aField = true but since the field is optional, I'd like to calculate the final value, like (aField = true) as final_value
• How could I use something like an IF anotherField = "a" THEN "OK" ELSE "NOT OK" ?

Maybe the documentation could have an index of all available functions... :)

us-west-1> select * from test_violations_new where HashKeyValue='*';
UnrecognizedClientException: Client is not recognized
Args: {'table_name': 'test_violations_new'}

us-west-1> scan test_violations_new;
UnrecognizedClientException: Client is not recognized
Args: {'table_name': 'test_violations_new'}

Is there a way to select elements from a nested map?
I have an item in a table table1 like-
NAME : { "FULL_NAME" :
{
"FNAME" : "foo",
"LNAME" : "bar"
}
}
I need to Select only FNAME from this table table1.

It can get too messy loading data into dynamodb using SQL statements. Was hoping if INSERT can load data from a csv file.

I would assume this to work but it is throwing a Engine: Parse Exception.

scan id, deleted_at from table-name where attribute_exists('deleted_at');

When storing query results as CSV, DQL prints the column names and then the entries on subsequent rows. It's enough to view the file in a CSV editor.

With JSON though, the output cannot be parsed as such. In order to do it I currently have to add commas after each line in the output (but not the last!), prepend the file with a [ and append a ].

The result of a SELECT/SCAN is a collection of rows, so it would be better to wrap the output in an array:

Meaning the output of SCAN foo, bar FROM table SAVE out.json could be similar to:

[
  { "foo": 1, "bar": 2},
  { "foo": 3, "bar": 4},
]

instead of

{ "foo": 1, "bar": 2}
{ "foo": 3, "bar": 4}

I could help implement this if you think it's worthwhile @stevearc. Though this would be a breaking change, unless we allow the behavior to be altered using a flag.

Let me know what you think!

I love this utility! Thank you!

I can load a data.dql file from REPL using file command.
us-east-1> file data.dql
Inserted 1 item

When I attempt to load it from CLI, i'm not sure how to do it.
$ dql -r us-east-1 -c "file data.dql"

.. did not work.

Any help is appreciated.

Running ls on a on-demand table causes this error.

us-east-1> ls golie-on-demand-table

I'm attempting to download, install, and run dql on Mac OS, with Python 2.7.10:

$ python --version
Python 2.7.10
$ cd $home
$ curl -o install.py https://raw.githubusercontent.com/stevearc/dql/master/bin/install.py
$ python install.py
$ ls -l | grep dql
-rwxr-xr-x 1 myusername staff 4101688 Oct 25 15:24 dql

So far so good, but when I try to launch dql, this is what I get (see below). Am I missing a pre-requisite, perhaps? The error at the bottom is:

any.whl/dynamo3/result.py", line 64, in
@six.python_2_unicode_compatible
AttributeError: 'module' object has no attribute 'python_2_unicode_compatible'

(Note that I have DynamoDB local running on this system also, if that matters)

$ ./dql -r us-east-1
Traceback (most recent call last):
File ".bootstrap/_pex/pex.py", line 326, in execute
File ".bootstrap/_pex/pex.py", line 258, in _wrap_coverage
File ".bootstrap/_pex/pex.py", line 290, in _wrap_profiling
File ".bootstrap/_pex/pex.py", line 369, in _execute
File ".bootstrap/_pex/pex.py", line 427, in execute_entry
File ".bootstrap/_pex/pex.py", line 441, in execute_pkg_resources
File ".bootstrap/pkg_resources/init.py", line 2235, in resolve
File "/Users/jfinnerty/.pex/install/dql-0.5.22-py2-none-any.whl.12892f07e4140cc0af170e642e8c1659b535b944/dql-0.5.22-py2-none-any.whl/dql/init.py", line 8, in
from .cli import DQLClient
File "/Users/jfinnerty/.pex/install/dql-0.5.22-py2-none-any.whl.12892f07e4140cc0af170e642e8c1659b535b944/dql-0.5.22-py2-none-any.whl/dql/cli.py", line 15, in
from .engine import FragmentEngine
File "/Users/jfinnerty/.pex/install/dql-0.5.22-py2-none-any.whl.12892f07e4140cc0af170e642e8c1659b535b944/dql-0.5.22-py2-none-any.whl/dql/engine.py", line 15, in
from dynamo3 import (TYPES, DynamoDBConnection, DynamoKey, LocalIndex,
File "/Users/jfinnerty/.pex/install/dynamo3-0.4.9-py2.py3-none-any.whl.b154b6913d278f7b90c1894c240209fbbc31c5c7/dynamo3-0.4.9-py2.py3-none-any.whl/dynamo3/init.py", line 2, in
from .batch import ItemUpdate
File "/Users/jfinnerty/.pex/install/dynamo3-0.4.9-py2.py3-none-any.whl.b154b6913d278f7b90c1894c240209fbbc31c5c7/dynamo3-0.4.9-py2.py3-none-any.whl/dynamo3/batch.py", line 7, in
from .result import ConsumedCapacity
File "/Users/jfinnerty/.pex/install/dynamo3-0.4.9-py2.py3-none-any.whl.b154b6913d278f7b90c1894c240209fbbc31c5c7/dynamo3-0.4.9-py2.py3-none-any.whl/dynamo3/result.py", line 64, in
@six.python_2_unicode_compatible
AttributeError: 'module' object has no attribute 'python_2_unicode_compatible'

As mentioned above, I have DynamoDB local running on my Mac, and I'd like to have dql connect with my local DynamoDB initially. How do I configure dql to communicate with my DynamoDB local versus my AWS account in the specified region?

Query History (This exists)

Given I have a dql shell running
And I run a query,
When I use up/down arrow keys,
Then I can scroll to the previous query. (aka query history). Helps in re-running query with out re-typing.

Retain query history across sessions (New Feature)

Given I have previously ran queries in dql session.
When I start a fresh dql session,
Then I want to be able to scroll to the queries from my previous session.

When trying to query a dynamo table which has a primary index called 'foo-bar' :

select * from my-prod-table where foo-bar = 'something';

it fails with the error:

ValidationException: Invalid KeyConditionExpression: Syntax error; token: "-", near: "foo-bar"

Any available workaround?

Thanks

I really love how I can write sql queries against dynamo. Eventhough it is not feature complete, it solves most of my regular usecases. I use this day-in and day-out for my job. It is soo much easier than using AWS dynamodb console.

Do you have any plans of extending the functionality or making this a library that can be built upon?

Some possible features additions that I will make heavy use of are:

1. query history
2. saved queries

How to call from php to dql?

> dql
Traceback (most recent call last):
  File "C:\Python27\Scripts\dql-script.py", line 9, in <module>
    load_entry_point('dql==0.5.11', 'console_scripts', 'dql')()
  File "c:\python27\lib\site-packages\pkg_resources\__init__.py", line 552, in load_entry_point
    return get_distribution(dist).load_entry_point(group, name)
  File "c:\python27\lib\site-packages\pkg_resources\__init__.py", line 2672, in load_entry_point
    return ep.load()
  File "c:\python27\lib\site-packages\pkg_resources\__init__.py", line 2345, in load
    return self.resolve()
  File "c:\python27\lib\site-packages\pkg_resources\__init__.py", line 2351, in resolve
    module = __import__(self.module_name, fromlist=['__name__'], level=0)
  File "c:\python27\lib\site-packages\dql\__init__.py", line 6, in <module>
    from .cli import DQLClient
  File "c:\python27\lib\site-packages\dql\cli.py", line 15, in <module>
    from .engine import FragmentEngine
  File "c:\python27\lib\site-packages\dql\engine.py", line 23, in <module>
    from .grammar import parser, line_parser
  File "c:\python27\lib\site-packages\dql\grammar\__init__.py", line 6, in <module>
    from .common import (from_, table, var, value, table_key, into, type_, upkey,
  File "c:\python27\lib\site-packages\dql\grammar\common.py", line 2, in <module>
    from pyparsing import (Word, Upcase, Optional, Combine, Group, alphas, nums,
ImportError: cannot import name Upcase

I get the same with Python 3. If I downgrade pyparsing to 1.5.7, I get a different error: "AttributeError: 'NoneType' object has no attribute 'setResultsName'". Could it be that you have a dependency versioning problem?

I frequently use clear. It would be nice to have the command built in.
The command would move whole page down till the prompt is at the top of the screen.

This is awesome work!

I am looking for something like this can run sql on top of dynamo. From the docs I only see the CLI but what I wanna is to integrate this with my python code. Is there any interface I can directly and safely reuse?

THANKS for help!

I would like the ability to search for tables matching a certain input string.
It would be even better if we can use regex as input.

Proposal: find <string|regex> or search <string|regex>
Output: The output would be the same as with ls, but a shorter list. And maybe coloring the matching part of the table name.
Note: This will have to work inside the interactive shell as well as with the option -c.

Workaround: This is current workaround for not having the built-in functionality.

AWS_PROFILE='my-profile' dql -r 'us-east-1' -c "ls" | grep 'word'

Please release the improvements so far.

I am not sure about the version number. I think it would be better to up the minor version to 0.6.0 as we have features going out.

Hi,

I'm running 0.5.23 (latest), and while querying "now()", it works fine
As soon as I modify my query to add an interval statement, it fails, even using the one from the documentation (NOW() - INTERVAL("1 day"))

Can you please let me know if interval function is no more supported/working ?

I wanted to play around with DQL locally, however the link in the "Developing" page seems to go nowhere:

https://dql.readthedocs.io/en/latest/topics/develop.html