Skip to content

Latest commit

 

History

History
executable file
·
172 lines (140 loc) · 5.41 KB

2020-02-27-using-sql-parser-module.md

File metadata and controls

executable file
·
172 lines (140 loc) · 5.41 KB
Raw
title author type date url sf_thumbnail_type sf_thumbnail_link_type sf_detail_type sf_page_title sf_page_title_style sf_no_breadcrumbs sf_page_title_bg sf_page_title_text_style sf_background_image_size sf_social_sharing sf_related_articles sf_sidebar_config sf_left_sidebar sf_right_sidebar sf_caption_position sf_remove_promo_bar ampforwp-amp-on-off categories
Re-using the JavaScript SQL Parser
Romain
post
2020-02-27 02:36:35 +0000
/blog/2020-02-27-using-sql-parser-module/
none
link_to_post
none
1
standard
1
none
light
cover
1
1
left-sidebar
Sidebar-2
Sidebar-1
caption-right
1
default
Version 4
Querying
Development

SQL autocompletion

The parser is running on the client side and comes with just a few megabytes of JavaScript that are then cached by the browser. This provides a very reactive & rich experience to the end users and allows to import it as a module dependency.

While the dynamic content like the list of tables, columns.. is obviously fetched via remote endpoints, all the SQL knowledge of the statements is available.

See the currently shipped SQL dialects.

npm package

What if I only want to use only the autocomplete as a JavaScript module in my own app?

Importing the Parser can be simply done as a npm package. Here is an example on how to use the parser in a Node.js demo app. There are two ways to get the module:

  • npm registry

Just get the module via:

npm install gethue

Will install the latest from https://www.npmjs.com/package/gethue. Then import the parser you need with something like below and run it on an SQL statement:

import sqlAutocompleteParser from 'gethue/parse/sql/hive/hiveAutocompleteParser';

const beforeCursor = 'SELECT col1, col2, tbl2.col3 FROM tbl; '; // Note extra space at end
const afterCursor = '';
const dialect = 'hive';
const debug = false;

console.log(
  JSON.stringify(
    sqlAutocompleteParser.parseSql(beforeCursor, afterCursor, dialect, debug),
    null,
    2
  )
);

Which then will output keywords suggestions and all the known locations:

{ locations:
  [ { type: 'statement', location: [Object] },
    { type: 'statementType',
      location: [Object],
      identifier: 'SELECT' },
    { type: 'selectList', missing: false, location: [Object] },
    { type: 'column',
      location: [Object],
      identifierChain: [Array],
      qualified: false,
      tables: [Array] },
    { type: 'column',
      location: [Object],
      identifierChain: [Array],
      qualified: false,
      tables: [Array] },
    { type: 'column',
      location: [Object],
      identifierChain: [Array],
      qualified: false,
      tables: [Array] },
    { type: 'table', location: [Object], identifierChain: [Array] },
    { type: 'whereClause', missing: true, location: [Object] },
    { type: 'limitClause', missing: true, location: [Object] } ],
  lowerCase: false,
  suggestKeywords:
  [ { value: 'ABORT', weight: -1 },
    { value: 'ALTER', weight: -1 },
    { value: 'ANALYZE TABLE', weight: -1 },
    { value: 'CREATE', weight: -1 },
    { value: 'DELETE', weight: -1 },
    { value: 'DESCRIBE', weight: -1 },
    { value: 'DROP', weight: -1 },
    { value: 'EXPLAIN', weight: -1 },
    { value: 'EXPORT', weight: -1 },
    { value: 'FROM', weight: -1 },
    { value: 'GRANT', weight: -1 },
    { value: 'IMPORT', weight: -1 },
    { value: 'INSERT', weight: -1 },
    { value: 'LOAD', weight: -1 },
    { value: 'MERGE', weight: -1 },
    { value: 'MSCK', weight: -1 },
    { value: 'RELOAD FUNCTION', weight: -1 },
    { value: 'RESET', weight: -1 },
    { value: 'REVOKE', weight: -1 },
    { value: 'SELECT', weight: -1 },
    { value: 'SET', weight: -1 },
    { value: 'SHOW', weight: -1 },
    { value: 'TRUNCATE', weight: -1 },
    { value: 'UPDATE', weight: -1 },
    { value: 'USE', weight: -1 },
    { value: 'WITH', weight: -1 } ],
  definitions: [] }
  • Local dependency

Checkout Hue and cd in the demo app:

cd tools/examples/api/hue_dep

npm install
npm run webpack
npm run app

In package.json there's a dependency on Hue:

"dependencies": {
  "hue": "file:../../.."
},

Now let's import the Hive parser:

import sqlAutocompleteParser from 'hue/desktop/core/src/desktop/js/parse/sql/hive/hiveAutocompleteParser';

SQL scratchpad

The lightweight SQL Editor also called "Quick Query" comes as a Web component. This one is still very new and will get better in the coming months.

"Mini SQL Editor component"

Any feedback or question? Feel free to comment here or on the Forum and quick start SQL querying!

Romain, from the Hue Team