You can translate the question and the replies:

python to run vql shell (select , create view, desc view)

Hi Team, I am using python to connect Denodo ad want to run some vql, I am using JDBC connection to connect denodo with python library "jaydebeapi", when I ran some select vql query, it works. But I encounter below two problems, can you help check: 1. when I ran "Desc view aaaa", it just show some information about column name and data type, size, but when I run it in VDP client, I can get more, for example it is and OPT or OBL column, how can I let this kind of information also shown from python query. I need this opt/OBL information. 2. When I run some "create or replace view....", the python script will return errors, how can I run "create or replace review..." statement through python scripts, do we have some examples? Thank you.
user
30-08-2020 11:56:42 -0400

1 Answer

Hello, Generally, the DESC statements like DESC VIEW would return the information about the views in a tabular format when executed from a different client tool (using JDBC, ODBC client tools). When the same VQL command is executed in the VQL Shell of the Virtual DataPort Administration Tool, it may display certain extra information which can be used for debugging purposes and this information may change in future. However, in order to get the information about the fields of a view which are OPT, OBL ,etc., then I would execute a VQL query like below: `DESC VQL VIEW <view_name>` This would display all the details of the particular view. You could take a look at the [Output Schema of the DESC Commands](https://community.denodo.com/docs/html/browse/latest/vdp/developer/appendix/output_schema_of_the_desc_commands/output_schema_of_the_desc_commands#output-schema-of-the-desc-commands) section under the Virtual DataPort Developer Guide for more details. Regarding creating new views using the Python connection, I was able to successfully create new views (derived view) by defining and executing the cursor. For example (assuming connection 'con' is already established using jaydebeapi): ``` query = "CREATE VIEW <view_name> as SELECT 1" cur = con.cursor() cur.execute(query) ``` After executing the above, new view was available in the Virtual DataPort. You could take a look at the [Syntax of the CREATE VIEW statement](https://community.denodo.com/docs/html/browse/latest/vdp/vql/defining_a_derived_view/defining_a_derived_view) section under the Virtual DataPort VQL Guide for more details. I would also suggest you to refer the Knowledge Base (KB) article [**How to connect to Denodo from Python - a starter for Data Scientists**](https://community.denodo.com/kb/view/document/How%20to%20connect%20to%20Denodo%20from%20Python%20-%20a%20starter%20for%20Data%20Scientists?category=Northbound+Connections) for additional information on the connection. Hope this helps!
Denodo Team
31-08-2020 07:20:47 -0400
You must sign in to add an answer. If you do not have an account, you can register here