MongoDBMongoDB

若要設定 MongoDB 連接,請提供 連接字串DB 名稱To set up a MongoDB connection, provide a Connection String and a DB Name.

連接字串Connection String

  • 最簡單的: mongodb://username:password@hostname:port/dbnameThe simplest: mongodb://username:password@hostname:port/dbname
  • 啟用 SSL 之後: mongodb://username:password@hostname:port/dbname?ssl=trueWith SSL enabled: mongodb://username:password@hostname:port/dbname?ssl=true
  • 啟用 SSL 和自我簽署憑證 (停用憑證驗證) : mongodb://username:password@hostname:port/dbname?ssl=true&ssl_cert_reqs=CERT_NONEWith SSL enabled and self-signed certificates (disables certificate verification): mongodb://username:password@hostname:port/dbname?ssl=true&ssl_cert_reqs=CERT_NONE

如有需要,您可以在查詢字串中傳遞額外的連接選項。If needed, you can pass additional connection options in the query string. 請參閱 [連接選項] (https://docs.mongodb.com/manual/reference/connection-string /#connection 選項) 中的完整詳細資料。See full details in [Connection Options](https://docs.mongodb.com/manual/reference/connection-string /#connection-options).

您可能會注意到,資料來源設定中的資料庫名稱有個別的欄位,而且我們也將它包含在連接字串中。You might notice that there is a separate field for the DB Name in the data source configuration and we also include it in the connection string. 這通常是 MLab 等共用主機上的必要項。This is usually required on shared hosts like MLab.

MongoDB AtlasMongoDB Atlas

因為它們位於共用環境,所以連接到 MongoDB 的可用層帳戶可能會有問題。Because they are on a shared environment connecting to MongoDB Atlas free tier accounts can be problematic. 為了獲得最佳結果,請使用格式的連接字串:For best results, use a connection string of the format:

mongodb+srv://<user>:<password>@<subdomain>.mongodb.net/<database>?retryWrites=true

疑難排解Troubleshooting

錯誤:「SSL 交握失敗: [SSL: CERTIFICATE_VERIFY_FAILED] 憑證驗證失敗」Error: “SSL handshake failed: [SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed”

當您的 MongoDB 伺服器使用自我簽署憑證時,通常就會發生這種情況。This usually happens when your MongoDB server is using self-signed certificates. 您可以切換至已正確簽署的憑證,或直接將 ssl_cert_reqs=CERT_NONE 選項新增至您的連接字串。You can either switch to a properly signed certificate or just add the ssl_cert_reqs=CERT_NONE option to your Connection String.

以 JSON 物件形式撰寫您的 MongoDB 查詢。Write your MongoDB query as a JSON object. 在執行期間,SQL 分析會將它轉換成 db.collection.find() 呼叫或 db.collection.aggregate() 呼叫。During execution, SQL Analytics will convert it into either a db.collection.find() call or a db.collection.aggregate() call. 以下是 JSON 物件對應並傳送至 MongoDB 的方式:Here’s how your JSON object is mapped and sent to MongoDB:

MongoDB 權杖MongoDB Token SQL 分析中的寫入位置Where to write in SQL Analytics
db 在 [資料來源設定] 畫面上On the data source setup screen
collection collection在查詢物件中新增索引鍵Add a collection key in your query object
query query在查詢物件中新增索引鍵Add a query key in your query object
projection fields在查詢物件中新增索引鍵Add a fields key in your query object
.sort() method sort在查詢物件中新增索引鍵Add a sort key in your query object
.skip() method skip在查詢物件中新增索引鍵Add a skip key in your query object
.limit() method limit在查詢物件中新增索引鍵Add a limit key in your query object
db.collection.count() 方法db.collection.count() method count在查詢物件中使用具有任何值的索引鍵Use a count key with any value in your query object

您用於每個金鑰的值會以未修改的形式傳遞給 MongoDB 的參數。The values you use for each key are passed unmodified as as parameters to MongoDB.

查詢範例Query Examples

簡單查詢範例Simple Query Example

{
  "collection": "my_collection",
  "query": {
    "type": 1
  },
  "fields": {
    "_id": 1,
    "name": 2
  },
  "sort": [{
    "name": "date",
    "direction": -1
  }]
}

在 JAVAscript 中會寫入對等的查詢: db.my_collection.find({"type": 1}, {"_id": 1, "name": 2}).sort([{"name": "date","direction": -1}])An equivalent query in Javascript would be written: db.my_collection.find({"type": 1}, {"_id": 1, "name": 2}).sort([{"name": "date","direction": -1}])

計數查詢範例Count Query Example

{
  "collection": "my_collection",
  "count": true
}

彙總Aggregation

匯總使用的語法類似于 PyMongo 中使用的語法。Aggregation uses a syntax similar to the one used in PyMongo. 不過,為了支援正確的排序次序,它會使用一般清單進行 "$sort" 作業,以在執行之前轉換成兒子 (排序的字典) 物件。However, to support the correct order of sorting, it uses a regular list for the “$sort” operation that converts into a SON (sorted dictionary) object before execution.

匯總查詢範例:Aggregation query example:

{
  "collection": "things",
  "aggregate": [{
    "$unwind": "$tags"
  }, {
    "$group": {
      "_id": "$tags",
      "count": {
        "$sum": 1
      }
    }
  }, {
    "$sort": [{
      "name": "count",
      "direction": -1
    }, {
      "name": "_id",
      "direction": -1
    }]
  }]
}

MongoDB 擴充 JSON 支援MongoDB Extended JSON Support

我們支援 MongoDB 擴充 JSON 以及我們自己的延伸模組 $humanTimeWe support MongoDB Extended JSON along with our own extension - $humanTime:

{
  "collection": "date_test",
  "query": {
    "lastModified": {
      "$gt": {
        "$humanTime": "3 years ago"
      }
    }
  },
  "limit": 100
}

它接受人們可讀取的字串,如上述 ( 「3年前」、「昨天」等等) 或時間戳記。It accepts a human-readable string like the above (“3 years ago”, “yesterday”, etc) or timestamps.

注意

$humanTime使用日期或日期/時間類型的查詢參數搭配 mongodb 時,也需要函式,原因是 SQL 分析使用的格式和 mongodb 預期的格式之間的差異。The $humanTime function is also needed when using Query Parameters of type Date or Date/Time with MongoDB, due to the difference between the format SQL Analytics uses and the one MongoDB expects.

使用日期 (或日期範圍) 參數時,請將其包裝在 $humanTime 物件中 {{param}}{"$humanTime": "{{param}} 00:00"} 只有在 00:00 日期參數的情況下,才需要 (尾碼,而針對日期時間參數,您應該略過它) 。When using a Date (or Date Range) parameter, wrap it with a $humanTime object: {{param}} becomes {"$humanTime": "{{param}} 00:00"} (the 00:00 suffix is needed only with Date parameters, for Date Time parameters you should skip it).

MongoDB 篩選MongoDB Filtering

您可以將篩選準則新增至結尾,藉以將篩選新增至 Mongo 查詢。You can add filters to Mongo queries by projecting a column with the ‘::filter’ keyword added on to the end.

{
  "collection": "zipcodes",
  "aggregate": [{
    "$project": {
      "_id": "$_id",
      "city": "$city",
      "loc": "$loc",
      "pop": "$pop",
      "state::filter": "$state"
    }
  }]
}

上述範例會顯示 [狀態] 資料行,並可讓您篩選此資料行。The above example will show a ‘State’ column, and allow you to filter on this column.

疑難排解Troubleshooting

排序超過104857600個位元組的記憶體限制Sort exceeded memory limit of 104857600 bytes

排序超過104857600個位元組的記憶體限制,但未加入外部排序。Sort exceeded memory limit of 104857600 bytes, but did not opt in to external sorting. 正在中止作業。Aborting operation. 傳遞 allowDiskUse:true 以加入宣告。Pass allowDiskUse:true to opt in.

在 MongoDB 中,記憶體中的排序有100M 的限制,若要執行大型排序,您需要啟用 allowDiskUse 選項,以將資料寫入暫存檔案以進行排序。In MongoDB, the in-memory sorting have a limit of 100M, to perform a large sort, you need enable allowDiskUse option to write data to a temporary files for sorting.

若要啟用此 allowDiskUse 選項,請直接將選項新增至您的查詢:To enable the allowDiskUse option, just add the option to your query:

{
  "allowDiskUse": true
}