如何使用 SQL 语言访问 netsuite 数据

How do I access netsuite data using SQL language

提问by shecode

A company uses netsuites to make purchase orders and store sales information. The only way this company is able to access this information at the moment is through building netsuites reports

一家公司使用网络套件来制作采购订单和存储销售信息。该公司目前能够访问此信息的唯一方法是通过构建网络套件报告

Is there a way SQL language could be written to access the netsuites data as some of the questions asked by the business need something a bit more complex than what netsuites reporting provides - and sound very easy using SQL language

有没有一种方法可以编写 SQL 语言来访问 netsuites 数据，因为业务提出的一些问题需要比 netsuites 报告提供的更复杂的东西 - 并且使用 SQL 语言听起来很容易

I have experience in Oracle, mysql, and many other SQL languages so I would like to know how to set up a connection to the companies netsuites data so I can help write custom queries

我有使用 Oracle、mysql 和许多其他 SQL 语言的经验，所以我想知道如何建立与公司 netsuites 数据的连接，以便我可以帮助编写自定义查询

回答by jchemp

Netsuite has SuiteAnalytics Connect, which is ODBC. It can be downloaded from a link at the very bottom the home screen of your Netsuite account.

Netsuite 具有 SuiteAnalytics Connect，即 ODBC。它可以从 Netsuite 帐户主屏幕底部的链接下载。

After installation of the application bundle and connecting it to your Netsuite, you can write ORACLE SQL queries via Excel, Access, etc. My experience is that Netsuite uses Oracle SQL in ODBC queries and in Saved Search custom queries.

安装应用程序包并将其连接到您的 Netsuite 后，您可以通过 Excel、Access 等编写 ORACLE SQL 查询。我的经验是 Netsuite 在 ODBC 查询和 Saved Search 自定义查询中使用 Oracle SQL。

回答by Jo O

The way I understand it, the ODBC driver is a separately priced item from NetSuite. Once you have that, you could use Excel, Access, Crystal Reports or anything else that can use the ODBC driver to read the data and write queries against it.

根据我的理解，ODBC 驱动程序是 NetSuite 单独定价的项目。一旦你有了它，你就可以使用 Excel、Access、Crystal Reports 或任何其他可以使用 ODBC 驱动程序来读取数据并针对它编写查询的东西。

The normal way to access NetSuite data is with SuiteScript -- javascript code that uses the nlapi* calls to get to the data.

访问 NetSuite 数据的正常方法是使用 SuiteScript——使用 nlapi* 调用获取数据的 JavaScript 代码。

回答by Saxman

You can access Netsuite backend using a number of different tools and it allows running Oracle SQL directly.

您可以使用多种不同的工具访问 Netsuite 后端，它允许直接运行 Oracle SQL。

Netsuite provides JDBC, ADO.NET and ODBC if it was included in your licensing purchase. You can find the downloads from the main menu - lower left hand of the screen in Settings - Set Up SuiteAnalytics Connect.

Netsuite 提供 JDBC、ADO.NET 和 ODBC，如果它包含在您购买的许可中。您可以从主菜单 - 屏幕左下方的设置 - 设置 SuiteAnalytics Connect 中找到下载。

ADO.net however is pretty worthless in SSIS as it doesn't allow parameter mapping or SQL from a variable value. I have yet to get the ODBC driver to work correctly - it can connect and show columns of a table but it won't validate saying "The ODBC Source.Outputs[ODBC Source Error Output].Columns[...] on the error output has properties that do not match the properties of its corresponding data source column."

然而，ADO.net 在 SSIS 中毫无价值，因为它不允许参数映射或来自变量值的 SQL。我还没有让 ODBC 驱动程序正常工作 - 它可以连接并显示表的列，但它不会验证说“The ODBC Source.Outputs[ODBC Source Error Output].Columns[...] on the error输出具有与其对应数据源列的属性不匹配的属性。”

The other options I'm looking into are Cozyroc and Kinsgwaysoft adapters for Netsuite. You could also do SOAP or REST connections if desired.

我正在研究的其他选项是用于 Netsuite 的 Cozyroc 和 Kinsgwaysoft 适配器。如果需要，您还可以进行 SOAP 或 REST 连接。

回答by Saxman

As previously mentioned, if you have it licensed you can download the ODBC driver from the Netsuite application. I tested some other adapters but found Netsuite's at least as good as the competitors and they provide timely security updates to the drivers as well as both 64 and 32 bit versions.

如前所述，如果您获得了许可，则可以从 Netsuite 应用程序下载 ODBC 驱动程序。我测试了其他一些适配器，但发现 Netsuite 至少与竞争对手一样好，它们为驱动程序以及 64 位和 32 位版本提供及时的安全更新。

There are also metadata browsers that are on the net (example is URL below), the type of browser being based on which kind of access you are looking at (web services, verses ODBC, etc). https://system.sandbox.netsuite.com/help/helpcenter/en_US/srbrowser/Browser2017_2/odbc/record/account.html

网络上还有元数据浏览器（示例是下面的 URL），浏览器的类型取决于您正在查看的访问类型（Web 服务、与 ODBC 等）。 https://system.sandbox.netsuite.com/help/helpcenter/en_US/srbrowser/Browser2017_2/odbc/record/account.html

Within the application GUI there is information you need to connect using their ODBC connection (you need the account id and the role id). The URL is just odbcserver.xxxx.netsuite.com where xxx is specific to your environment you are accessing. Note that other adapters such as Cozyroc, etc. require the admin to setup connections in Netsuite for those. Here is a list of the main metadata tables:

在应用程序 GUI 中有您需要使用其 ODBC 连接进行连接的信息（您需要帐户 ID 和角色 ID）。URL 只是 odbcserver.xxxx.netsuite.com，其中 xxx 特定于您正在访问的环境。请注意，其他适配器（例如 Cozyroc 等）需要管理员在 Netsuite 中为这些适配器设置连接。以下是主要元数据表的列表：

• OA_FKEYS
• OA_TABLES
• OA_COLUMNS

• OA_FKEYS
• OA_TABLES
• OA_COLUMNS

In addition, note that the URL above does NOT include customization. Plus its not easily consumed by automation tools. But Netsuite provides a set of metadata views within the repository which you can use ODBC to pull from which includes all keys - foreign and primary. AND it does include customizations! I built simple set of ETL jobs that use standard ADO.NET driver within SSIS and the ODBC DSN to pull all the metadata and then use BIML to automatically generate all the extract ETL and related staging tables. You can also easily then use the metadata to detect changes in the underlying database.

此外，请注意上面的 URL 不包括自定义。另外它不容易被自动化工具消耗。但是 Netsuite 在存储库中提供了一组元数据视图，您可以使用 ODBC 从中提取包括所有键 - 外键和主键。而且它确实包括自定义！我构建了一组简单的 ETL 作业，它们使用 SSIS 中的标准 ADO.NET 驱动程序和 ODBC DSN 来提取所有元数据，然后使用 BIML 自动生成所有提取 ETL 和相关的临时表。然后，您还可以轻松地使用元数据来检测底层数据库中的更改。

Let me know if I can be any help with doing any of this.

让我知道我是否能帮上忙。

回答by Aaron Hinni

I've not used this, but there should as of 2014.2 there is an ODBC driver available for readaccess.

我没有使用过这个，但是从 2014.2 开始应该有一个 ODBC 驱动程序可用于读取访问。

http://www.netsuite.com/portal/landing/2014-2/suiteanalytics.shtml

http://www.netsuite.com/portal/landing/2014-2/suiteanalytics.shtml

回答by Bill Border

There are 3rd party tools, like CData ODBC Adapter for NetSuite: http://www.cdata.com/drivers/netsuite/odbc/. This tool allows you to run standard SQL against the NetSuite API. Note that the NetSuite API presents a very different data model from that of the native NetSuite ODBC.

有 3rd 方工具，例如 NetSuite 的 CData ODBC Adapter：http://www.cdata.com/drivers/netsuite/odbc/ 。此工具允许您针对 NetSuite API 运行标准 SQL。请注意，NetSuite API 呈现与原生 NetSuite ODBC 非常不同的数据模型。

With this tool, you install in onto your machine (Win in my case) and then after you configure the driver, you can run SQL against it (the driver config is where you specify your NS credentials, etc.).

使用此工具，您可以将其安装到您的机器上（在我的情况下是 Win），然后在配置驱动程序后，您可以对其运行 SQL（驱动程序配置是您指定 NS 凭据等的地方）。

It revolutionizes access to NetSuite data IMHO.

恕我直言，它彻底改变了对 NetSuite 数据的访问。

回答by adamc

Download the Netsuite ODBC drivers, set up your DSN locally with server/credentials, Use SSIS pkgs to automate data extract and load onto you local DB. Use ADO.NET drivers if preferred. Then utilize those tables to create/deliver your complex reports using SQL (stored procedures) and Reporting Services. This will require SQL Standard license at minimum though.

下载 Netsuite ODBC 驱动程序，使用服务器/凭据在本地设置 DSN，使用 SSIS pkgs 自动提取数据并加载到本地数据库。如果愿意，请使用 ADO.NET 驱动程序。然后利用这些表使用 SQL（存储过程）和 Reporting Services 创建/交付复杂的报告。但这至少需要 SQL Standard 许可证。

回答by Jimenez

As an alternative to different tools that were already mentioned you can consider using Skyvia Query. It allows you to perform all type of SQL queries directly to NetSuite. You can either type SQL queries manually, or use visual query builder to simply drag and drop the necessary fields.

作为已经提到的不同工具的替代方案，您可以考虑使用Skyvia Query。它允许您直接对 NetSuite 执行所有类型的 SQL 查询。您可以手动键入 SQL 查询，也可以使用可视化查询构建器来简单地拖放必要的字段。

回答by Anil Darji

Only API call can access the NetSuite data

只有 API 调用才能访问 NetSuite 数据

回答by Grant Taylor

You can't access to Netsuite data using sql language.

您无法使用 sql 语言访问 Netsuite 数据。

The only way to gain access to Netsuite from third party systems is to use SOAP request or PHP toolkit.

从第三方系统访问 Netsuite 的唯一方法是使用 SOAP 请求或 PHP 工具包。