Dialogflow与BigQuery集成 - 向BQ传递整数参数

Question

我正在创建一个聊天机器人，从BigQuery检索数据，但我在Dialogflow中的数据类型遇到了问题：

Dialogflow参数是：Rating(@sys.number-int)，Country(string)，Department(String)

当我执行下面的代码时，似乎bigquery将Rating作为字符串接收（Country和Department是字符串，在BigQuery中正常工作），所以我尝试过CAST，但没有成功。

有人能帮我从Dialogflow传递INT64变量到BigQuery吗？

 function buyAgainPredictor(agent) {
    const OPTIONS = {
                query: 'WITH pred_table AS (SELECT CAST(`request.body.queryResult.outputContexts[0].parameters.Rating´ AS INT64) as Rating, "request.body.queryResult.outputContexts[0].parameters.Department" as Department,  "request.body.queryResult.outputContexts[0].parameters.Country" as Country)' +
                'SELECT cast(predicted_label as INT64) as predicted_label ' +
                'FROM ML.PREDICT(MODEL Customer_feedback.recommend_model,  TABLE pred_table)',
                timeoutMs: 10000,
                useLegacySql: false,
                queryParameters: {}

DialogFlow参数定义

- Yabmad

---

你认为为什么它会将其接收为字符串？ - Martin Weitzmann

Answer 1

你没有在字符串中替换变量。尝试在query中使用以下代码：

`WITH pred_table AS (
  SELECT ${request.body.queryResult.outputContexts[0].parameters.Rating} as Rating,
     "${request.body.queryResult.outputContexts[0].parameters.Department}" as Department,
     "${request.body.queryResult.outputContexts[0].parameters.Country}" as Country)

SELECT cast(predicted_label as INT64) as predicted_label 
     FROM ML.PREDICT(MODEL Customer_feedback.recommend_model, TABLE pred_table)`

通常情况下，您不会想在 SQL 查询中使用用户输入（SQL 注入），请查看 参数化查询 的文档。考虑到这一点，您可以尝试以下方法：

const OPTIONS = {
    query: `WITH pred_table AS (
                SELECT @rating as Rating,
                       @department as Department,  
                       @country as Country)

            SELECT cast(predicted_label as INT64) as predicted_label
                 FROM ML.PREDICT(MODEL Customer_feedback.recommend_model, TABLE pred_table)`,

    timeoutMs: 10000,
    useLegacySql: false,
    queryParameters: {
        rating: parameters.Rating,
        department: parameters.Department,
        country: parameters.Country
    }
}

- Tlaquetzal