用于在两个 Teradata 服务器之间复制数据的 Bteq 脚本
如何将数据从一个数据库中的多个表复制到驻留在不同服务器上的另一个数据库?
这可以通过 Teradata 中的 BTEQ 脚本实现吗?
如果是这样,请提供样品。
如果没有,除了使用平面文件之外还有其他选择吗?
How do I copy data from multiple tables within one database to another database residing on a different server?
Is this possible through a BTEQ Script in Teradata?
If so, provide a sample.
If not, are there other options to do this other than using a flat-file?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(4)
使用 BTEQ 是不可能的,因为您已经提到两个数据库驻留在不同的服务器中。
对此有两种解决方案。
Arcmain - 您需要首先使用 Arcmain Backup,它会创建包含表中数据的文件。然后,您需要使用 Arcmain 恢复来恢复文件中的数据
TPT - Teradata Parallel Transporter 。这是一个非常先进的工具。这不会创建任何像 Arcmain.txt 这样的文件。它直接在两个 Teradata 服务器之间移动数据。(维基百科)
This is not possible using BTEQ since you have mentioned both the databases are residing in different servers.
There are two solutions for this.
Arcmain - You need to use Arcmain Backup first, which creates files containing data from your tables. Then you need to use Arcmain restore which restores the data from the files
TPT - Teradata Parallel Transporter. This is a very advanced tool. This does not create any files like Arcmain. It directly moves the data between two teradata servers.(Wikipedia)
如果我理解你的问题,你想将一组表从一个数据库移动到另一个数据库。
您可以在 BTEQ 脚本中使用以下语法来复制表和数据:
CREATE TABLE.AS.WITH DATA AND STATS;
或者只是表结构:
CREATE TABLE.AS.NO DATA AND NO STATS;
如果您真正懂行,您可以创建一个 BTEQ 脚本,在 SELECT 语句中动态构建上述语句,导出结果,然后依次运行新导出的文件,所有这些都在单个 BTEQ 中进行脚本。
您还可以使用
CREATE TABLE <...> 执行许多其他选项。 AS <...>;
。您最好查看 Teradata 手册以了解更多详细信息。If I am understanding your question, you want to move a set of tables from one DB to another.
You can use the following syntax in a BTEQ Script to copy the tables and data:
CREATE TABLE <NewDB>.<NewTable> AS <OldDB>.<OldTable> WITH DATA AND STATS;
Or just the table structures:
CREATE TABLE <NewDB>.<NewTable> AS <OldDB>.<OldTable> WITH NO DATA AND NO STATS;
If you get real savvy you can create a BTEQ script that dynamically builds the above statement in a SELECT statement, exports the results, then in turn runs the newly exported file all within a single BTEQ script.
There are a bunch of other options that you can do with
CREATE TABLE <...> AS <...>;
. You would be best served reviewing the Teradata Manuals for more details.还有一些选项可以让您从一个表复制到另一个表。
最简单的方法可能是编写一个小型程序,使用其通信层之一(ODBC、.NET 数据提供程序、JDBC、cli 等),并使用它来获取 select 语句和 insert 语句。这需要一些工作,但比尝试学习如何编写 TPT 脚本的开销要少。您不需要任何“DBA”权限即可自行编写。
Teradata 还销售其他应用程序,这些应用程序隐藏了某些工具的复杂性。 Teradata Data Mover 句柄在 arcmain 和 tpt 等工具之间提供了一个抽象层。访问此工具很可能仅限于 DBA 类型。
There are a few more options which will allow you to copy from one table to another.
Possibly the simplest way would be to write a smallish program which uses one of their communication layers (ODBC, .NET Data Provider, JDBC, cli, etc.) and use that to take a select statement and an insert statement. This would require some work, but it would have less overhead than trying to learn how to write TPT scripts. You would not need any 'DBA' permissions to write your own.
Teradata also sells other applications which hides the complexity of some of the tools. Teradata Data Mover handles provides an abstraction layer between tools like arcmain and tpt. Access to this tool is most likely restricted to DBA types.
如果您想将数据从一台服务器移动到另一台服务器,那么
我们可以使用平面文件来做到这一点。
首先,我们通过任何实用程序(例如 bteq 或 fastexport)将数据从源表获取到平面文件。
然后我们可以借助 mload、fastload 或 bteq 脚本将此数据加载到目标表中。
If you want to move data from one server to another server then
We can do this with the flat file.
First we have fetch data from source table to flat file through any utility such as bteq or fastexport.
then we can load this data into target table with the help of mload,fastload or bteq scripts.