MLSQL智慧程式碼提示

語言: CN / TW / HK

MLSQL智慧補全功能現階段是作為MLSQL的一個外掛的形式提供的。在釋出第一個版本後,我們會將其獨立出來,作為一個通用的SQL提示引擎來進行後續的發展。為了方便對該專案指代,我們後續使用 【MLSQL Code Intelligence】

專案地址: mlsql-autosuggest

當前狀態

【積極開發中,還未釋出穩定版本】

目標

【MLSQL Code Intelligence】目標分成兩個,第一個是標準SQL補全:

  1. SQL關鍵字補全
  2. 表/欄位屬性/函式補全
  3. 可二次開發自定義對接任何Schema Provider

第二個是MLSQL語法補全:

  1. 支援各種資料來源提示
  2. 支援臨時表提示
  3. 支援各種ET元件引數提示以及名稱提示

對於表和欄位補,函式補全,相比其他一些SQL程式碼提示工具,該外掛可根據當前已有的資訊精確推斷。比如:

select  no_result_type, keywords, search_num, rank
from(
  select  [CURSOR is HERE] row_number() over (PARTITION BY no_result_type order by search_num desc) as rank
  from(
    select jack1.*,no_result_type, keywords, sum(search_num) AS search_num
    from jack.drugs_bad_case_di as jack1,jack.abc jack2
    where hp_stat_date >= date_sub(current_date,30)
    and action_dt >= date_sub(current_date,30)
    and action_type = 'search'
    and length(keywords) > 1
    and (split(av, '\\.')[0] >= 11 OR (split(av, '\\.')[0] = 10 AND split(av, '\\.')[1] = 9))
    --and no_result_type = 'indication'
    group by no_result_type, keywords
  )a
)b
where rank <=

滑鼠在第三行第十列,此時系統會自動提示:

  1. a [表名]
  2. jack1展開的所有列
  3. no_result_type
  4. keywords
  5. search_num

如果有介面提供schema資訊,會自動展開*,並且獲取相關層級的資訊從而非常精準的進行提示。同時,如果有shcema資訊,對每個欄位也支援型別提示。外掛提供了非常友好和簡單的介面方便使用者接入自己的元資料。

使用者指南

部署

參考部署文件MLSQL部署

該外掛作為MLSQ預設外掛,所以開箱即用

介面使用

訪問介面: http://127.0.0.1:9003/run/script?executeMode=autoSuggest

引數1: sql SQL指令碼

引數2: lineNum 游標所在的行號 從1開始計數

引數3: columnNum 游標所在的列號,從1開始計數

比如我用Scala寫一個client:

object Test {
  def main(args: Array[String]): Unit = {
    val time = System.currentTimeMillis()
    val response = Request.Post("http://127.0.0.1:9003/run/script").bodyForm(
      Form.form().add("executeMode", "autoSuggest").add("sql",
        """
          |select spl  from jack.drugs_bad_case_di as a
          |""".stripMargin).add("lineNum", "2").add("columnNum", "10").build()
    ).execute().returnContent().asString()
    println(System.currentTimeMillis() - time)
    println(response)
  }

}

最後結果如下:

[{"name":"split",
"metaTable":{"key":{"db":"__FUNC__","table":"split"},
"columns":[
{"name":null,"dataType":"array","isNull":true,"extra":{"zhDoc":"\nsplit函式。用於切割字串,返回字串陣列\n"}},{"name":"str","dataType":"string","isNull":false,"extra":{"zhDoc":"待切割字元"}},
{"name":"pattern","dataType":"string","isNull":false,"extra":{"zhDoc":"分隔符"}}]},
"extra":{}}]

可以知道提示了split,並且這是一個函式,函式的引數以及返回值都有定義。

程式設計開發

首先初始化兩個此法分析器:

object AutoSuggestController {
  val lexerAndParserfactory = new ReflectionLexerAndParserFactory(classOf[DSLSQLLexer], classOf[DSLSQLParser]);
  val mlsqlLexer = new LexerWrapper(lexerAndParserfactory, new DefaultToCharStream)

  val lexerAndParserfactory2 = new ReflectionLexerAndParserFactory(classOf[SqlBaseLexer], classOf[SqlBaseParser]);
  val sqlLexer = new LexerWrapper(lexerAndParserfactory2, new RawSQLToCharStream)

}

接著建立AutoSuggestContext,然後用此法分析器解析sql,最後傳遞給context,同時傳遞行號和列好,即可。

val sql = params("sql")
val lineNum = params("lineNum").toInt
val columnNum = params("columnNum").toInt

val context = new AutoSuggestContext(ScriptSQLExec.context().execListener.sparkSession,
  AutoSuggestController.mlsqlLexer,
  AutoSuggestController.sqlLexer)

val sqlTokens = context.lexer.tokenizeNonDefaultChannel(sql).tokens.asScala.toList

val tokenPos = LexerUtils.toTokenPos(sqlTokens, lineNum, columnNum)
JSONTool.toJsonStr(context.build(sqlTokens).suggest(tokenPos))

開發者指南

解析流程

【MLSQL Code Intelligence】複用了MLSQL/Spark SQL的lexer,重寫了parser部分。因為程式碼提示有其自身特點,就是句法在書寫過程中,大部分情況下都是錯誤的,無法使用嚴格的parser來進行解析。

使用兩個Lexer的原因是因為,MLSQL Lexer主要用來解析整個MLSQL指令碼,Spark SQL Lexer主要用來解決標準SQL中的select語句。但是因為該專案高度可擴充套件,使用者也可以自行擴充套件到其他標準SQL的語句中。

以select語句裡的程式碼提示為例,整個解析流程為:

  1. 使用MLSQL Lexer 將指令碼切分成多個statement
  2. 每個statement 會使用不同的Suggester進行下一步解析
  3. 使用SelectSuggester 對select statement進行解析
  4. 首先對select語句構建一個非常粗粒度的AST,節點為每個子查詢,同時構建一個表結構層級快取資訊TABLE_INFO
  5. 將游標位置轉化為全域性TokenPos
  6. 將全域性TokenPos轉化select語句相對TokenPos
  7. 根據TokenPos遍歷Select AST樹,定位到簡單子語句
  8. 使用project/where/groupby/on/having子suggester進行匹配,匹配的suggester最後完成提示邏輯

在AST樹種,每個子語句都可以是不完整的。由上面流程可知,我們會以statement為粗粒度工作context,然後對於複雜的select語句,最後我們會進一步細化到每個子查詢為工作context。這樣為我們編碼帶來了非常大的便利。

TokenMatcher工具類

在【MLSQL Code Intelligence】中,最主要的工作是做token匹配。我們提供了TokenMatcher來完成token的匹配。TokenMatcher支援前向和後向匹配。如下token序列:

select a , b , c from jack

假設我想以token index 3(b) 為起始點,前向匹配一個逗號,identify 可以使用如下語法:

val tokenMatcher = TokenMatcher(tokens,4).forward.eat(Food(None, TokenTypeWrapper.DOT), Food(None, SqlBaseLexer.IDENTIFIER)).build

接著你可以呼叫 tokenMatcher.isSuccess來判斷是否匹配成功,可以呼叫tokenMatcher.get 獲取匹配得到匹配成功後的index,通過tokenMatcher.getMatchTokens 獲取匹配成功的token集合。

注意,TokenMatcher起始位置是包含的,也就是他會將起始位置的token也加入到匹配token裡去。所以在上面的例子中,start 是4而不是3. 更多例子可以檢視原始碼。

快速參與貢獻該專案

【MLSQL Code Intelligence】 需要大量函式的定義,方便在使用者使用時給予提示。下面是我實現的 split 函式的程式碼:

class Splitter extends FuncReg {

  override def register = {
    val func = MLSQLSQLFunction.apply("split").
      funcParam.
      param("str", DataType.STRING, false, Map("zhDoc" -> "待切割字元")).
      param("pattern", DataType.STRING, false, Map("zhDoc" -> "分隔符")).
      func.
      returnParam(DataType.ARRAY, true, Map(
        "zhDoc" ->
          """
            |split函式。用於切割字串,返回字串陣列
            |""".stripMargin
      )).
      build
    func
  }

}

使用者只要用FunctionBuilder去構建函式簽名即可。這樣使用者在使用該函式的時候就能得到非常詳盡的使用說明和引數說明。同時,我們也可以通過該函式簽名獲取巢狀函式處理後的欄位的型別資訊。

使用者只要按上面的方式新增更多函式到tech.mlsql.autosuggest.funcs包下即可。系統會自動掃描該包裡的實現並且註冊。

子查詢層級結構

對於語句:

select  no_result_type, keywords, search_num, rank
from(
  select  keywords, search_num, row_number() over (PARTITION BY no_result_type order by search_num desc) as rank
  from(
    select *,no_result_type, keywords, sum(search_num) AS search_num
    from jack.drugs_bad_case_di,jack.abc jack
    where hp_stat_date >= date_sub(current_date,30)
    and action_dt >= date_sub(current_date,30)
    and action_type = 'search'
    and length(keywords) > 1
    and (split(av, '\\.')[0] >= 11 OR (split(av, '\\.')[0] = 10 AND split(av, '\\.')[1] = 9))
    --and no_result_type = 'indication'
    group by no_result_type, keywords
  )a
)b
where rank <=

形成的AST結構樹如下:

select no_result_type , keywords , search_num , rank from ( select keywords , search_num , row_number ( ) over
 ( PARTITION BY no_result_type order by search_num desc ) as rank from ( select * , no_result_type , keywords ,
 sum ( search_num ) AS search_num from jack . drugs_bad_case_di , jack . abc jack where hp_stat_date >= date_sub (
 current_date , 30 ) and action_dt >= date_sub ( current_date , 30 ) and action_type = 'search' and length (
 keywords ) > 1 and ( split ( av , '\\.' ) [ 0 ] >= 11 OR ( split
 ( av , '\\.' ) [ 0 ] = 10 AND split ( av , '\\.' ) [ 1 ]
 = 9 ) ) group by no_result_type , keywords ) a ) b where rank <=


=>select keywords , search_num , row_number ( ) over ( PARTITION BY no_result_type order by search_num desc ) as
 rank from ( select * , no_result_type , keywords , sum ( search_num ) AS search_num from jack . drugs_bad_case_di
 , jack . abc jack where hp_stat_date >= date_sub ( current_date , 30 ) and action_dt >= date_sub ( current_date
 , 30 ) and action_type = 'search' and length ( keywords ) > 1 and ( split ( av ,
 '\\.' ) [ 0 ] >= 11 OR ( split ( av , '\\.' ) [ 0 ] = 10
 AND split ( av , '\\.' ) [ 1 ] = 9 ) ) group by no_result_type , keywords )
 a ) b


==>select * , no_result_type , keywords , sum ( search_num ) AS search_num from jack . drugs_bad_case_di , jack
 . abc jack where hp_stat_date >= date_sub ( current_date , 30 ) and action_dt >= date_sub ( current_date , 30
 ) and action_type = 'search' and length ( keywords ) > 1 and ( split ( av , '\\.' )
 [ 0 ] >= 11 OR ( split ( av , '\\.' ) [ 0 ] = 10 AND split
 ( av , '\\.' ) [ 1 ] = 9 ) ) group by no_result_type , keywords ) a

我們可以看到一共嵌套了兩層,每層都有一個子查詢。

對此形成的TABLE_INFO結構如下:

2:
List(
MetaTableKeyWrapper(MetaTableKey(None,Some(jack),drugs_bad_case_di),None), 
MetaTableKeyWrapper(MetaTableKey(None,None,null),Some(a)), 
MetaTableKeyWrapper(MetaTableKey(None,Some(jack),abc),Some(jack)))
1:
List(MetaTableKeyWrapper(MetaTableKey(None,None,null),Some(b)))
0:
List()

0層級為最外層語句;1層級為第一個子查詢;2層級為第二個子查詢,他包含了子查詢別名以及該子查詢裡所有的實體表資訊。

上面只是為了顯示,實際上還包含了所有列的資訊。這意味著,如果我要補全0層記得 project,那我只需要獲取1層級的資訊,可以補全b表名稱或者b表對應的欄位。同理類推。