When ArcGIS10.2 encounters Teradata

With the continuous development of computer technology, GIS technology is also keeping up with the upsurge of IT technology. From 3D technology to mobile technology, from big data technology to cloud computing technology, as long as IT has new technologies, esri will keep up with the latest technology trends. As a main practitioner of data warehouse, Teradata is widely used by users and has a good reputation. Teradata

With the continuous development of computer technology, GIS technology is also keeping up with the upsurge of IT technology. From 3D technology to mobile technology, from big data technology to cloud computing technology, as long as IT has new technologies, esri will keep up with the latest technology trends. As a main practitioner of data warehouse, Teradata is widely used by users and has a good reputation. Teradata

With the continuous development of computer technology, GIS technology is also keeping up with the upsurge of IT technology. From 3D technology to mobile technology, from big data technology to cloud computing technology, as long as IT has new technologies, esri will keep up with the latest technology trends.

As a main practitioner of data warehouse, Teradata is widely used by users and has a good reputation.

Teradata Data Warehouse Introduction

Teradata Data Warehouse has world-leading technologies. Its main software and hardware products include: Teradata database, Teradata data Data Warehouse software, enterprise data warehouse, dynamic enterprise data warehouse, and data warehouse platform.
The Teradata Data Warehouse is equipped with the most efficient and reliable massively parallel processing (MPP) platform to process massive data at high speed. It enables enterprises to focus on their businesses without having to spend a lot of energy on management technologies. Therefore, they can make informed decisions more quickly to maximize ROI. Teradata is organized according to the Shared Nothing architecture system as a whole. It is positioned as a large data warehouse system. Its positioning is relatively high, and its hardware and software are all NCR's own, others are not identified; therefore, general enterprises cannot afford it and the price is very expensive. Since Teradata is usually used in OLAP applications, the single-host Teradata system is rare. Even a single-host system, the Teradata architecture is recommended to provide better database performance as much as possible.

Typical Case: The data warehouse of Shanghai Stock Exchange is teradata. I/O per second can reach 2 GB, with more than 1000 disks and expensive hardware. A full table scans tens of millions of records in seconds.

Teradata's support for Spatial Data

Teradata added a space extension package in Release 13.0, providing the ability to manage and operate space data for the TD database [1].

In general, Teradata GeoSpatial extensions include

1) A series of spatial data types: Point, Line, Polygon, and other spatial data types;

2) data types are implemented using UDT and stored in SYSUDLIB of Teradata;

3) data type operation functions use open source space libraries Geos, Proj, GDdal/Ogr;

4) SYSSPATIAL stores all metadata tables and spatial functions;

5) The TDGEOINPORT tool supports data from common spatial data formats (Shape, TAB ...) Load data to the TD database.

In addition, the current TD space data supports direct visualization of some GIS data visualization tools.

1) Data Loading: Spatial data can be loaded using the existing td etl loading tool (such as MultiLoad). It supports spatial data in WKB and WKT formats. In addition, TD can also use TDGEOIMPORT to load files in common spatial data formats, such as ESRI Shape files.
2) function: SYSSPATIAL contains a series of function sets, stored procedures, and data tables for processing spatial data.
3) data type: the database SYSUDTLIB contains definitions of all spatial data types. The data type definition includes the structure of the spatial data type and the operational functions that can be operated on.
4) Query Language: You can use SQL and Teradata client APIs and query tools to access spatial data types through standard SQL interfaces.

2. Data Types and operations

For data types, Teradata Spatial is compatible with SQL/MM standards.

SQL/MM Geometry Hierarchy

3. spatial metadata

The SYSSPATIAL database actually has two types of spatial metadata. The SYSSPATIAL. GEOMETRY_COLUMNS table stores information about all data tables that contain spatial columns. This table is maintained using the AddGeometryColumn and DropGeometryColumn stored procedures. SYSSPATIAL. SPATIAL_REF_SYS bucket reference information. This table is initialized during installation. SRS information stored in this table will be used by ST_TRANSFORM.

4. Data construction and uploading

Spatial data can be loaded to the database using any data upload tool or client API. WKB and WKT construction methods are supported for spatial data construction. TDGEOIMPORT is a windows client tool that converts the spatial data format to a format compatible with the Teradata loading tool.

5. spatial indexes

Supports Tessellate indexes, which are essentially multi-level grid indexes. You need to enter and adjust the parameters for grid partitioning. The R-tree Index is not supported.

In version 1.5, the space expansion of Teradata GeoSpatial Release is relatively limited in terms of functionality and implementation compared with Spatial expansion such as PostGIS and Oracle Spatial. The improvements in the kernel for the path selection of the space data optimizer, the processing of Space connections in the MPP environment, and the execution of space operators are not covered in this version, we also hope that more improvements and enhancements will be made to the TD Spatial Release version in the future. In use, the SYSSPATIAL database actually has two types of spatial metadata. The SYSSPATIAL. GEOMETRY_COLUMNS table stores information about all data tables that contain spatial columns. This table is maintained using the AddGeometryColumn and DropGeometryColumn stored procedures. SYSSPATIAL. SPATIAL_REF_SYS bucket reference information. This table is initialized during installation. SRS information stored in this table will be used by ST_TRANSFORM.

========================================================== ========================================================== ============================

TeraData and ArcGIS10.2

The three most important products of Data Warehouse include exdata of Oracle, netezza of IBM, and teradata. Both of them are presented to the customer in the form of an all-in-one machine, that is, hardware and software, the latter may not need to bind hardware based on the information I have found and its own practices (because I downloaded the relevant virtual machine on the official website, which is just a guess ), moreover, ArcGIS has supported IBM's netezza in version 10, so this article mainly aims to see how Esri's ArcGIS is combined with Teradata.

First understand, ArcGIS10.2 on teradata system requirements: http://resources.arcgis.com/en/help/system-requirements/10.2/index.html#//015100000090000000

From the system requirements, we can see that ArcGIS10.2 only supports Teradata14.0 and later versions. Like other database products, NoSQL and SQLite only support native storage and ST_geometry storage. Geodatabase is not supported currently, it also does not support element services.

System Environment

• ArcGIS10.2
• Teradata Server
• Teradata ICU library
• Teradata GSS client package
• Teradata ODBC driver

Related to Teradata:

• Teradata server: http://downloads.teradata.com/download/database/teradata-express/vmware
• For Windows, see http://downloads.teradata.com/download/connectivity/odbc-driver/windows
• For Linux, see http://downloads.teradata.com/download/connectivity/odbc-driver/linux

Note:

For ICU, GSS, and ODBC, download the SDK based on the number of digits on the client. If you use ArcGIS for topics top10.2 (32Bit), you must download the 32Bit package. If you use ArcGIS for Server 10.2 (64Bit) 64-bit packages should be downloaded.

The Teradata server can download the vmvm (Suse operating system, Teradata has been installed, Default User root/root). For more details, see the following documents:

Environment Configuration

Because my test environment is Windows + ArcGIS for topics top10.2 + Teradata, I downloaded the Windows Teradata package.

The installation sequence after the download is completed:

1. Install Teradata ICU library and GSS client on the ArcGIS client
2: then install Teradata ODBC driver
Configure the ODBC driver (Note: To download the 32Bit package, you must configure the 32Bit ODBC)

• On Windows operating systems, change the following settings in the set the Teradata ODBC Driver Options dialog box:
• Set the session mode to Teradata.
• Select the option to Use Native Large Object Support.
• On Linux operating systems, set the following parameters in the odbc. ini file:
• SessionMode = Teradata
• UseNativeLOBSupport = Yes

The configuration of ArcGIS Server reference: http://resources.arcgis.com/en/help/main/10.2/index.html#/Set_up_a_connection_to_Teradata/019v0000001s000000/

Open the 32Bit ODBC and add the teradata driver

Enter the DataSource name. The default value is teradata. Enter the IP address, Username, and password of the Teradata server of the VM, enter dbc, and click Options.

Set the Session Mode and select Use Native Large Object Support.

After setting the preceding information, we can use ArcGIS for connecting top10.2 to connect to teradata and set the connection parameters.

Data Source: Enter the teradata that we entered above by default. You can enter the default dbc/dbc for the user name and password (if odbc entered the user name and password above, you can use the operating system mode, this dbc can be analogous to Oracle's sys/system)

Double-click the connection and you will see that the connection is connected.

========================================================== ======================================

ArcGIS10.2 Teradata

Next we will use ArcGIS for Desktop 10.2 to create or import data to Teradata.

1: first, I connected to Super User dbc in Teradata and created an sde user. The permanent tablespace is 200 mb, and the spool space cannot exceed 100 mb.

Create user sde as perm=200000000，spool=100000000，password=sde；

For more basic Teradata operations, refer to: http://oracleabc-126-com.iteye.com/blog/1195986

2: configure the connection interface

3: Create a feature class. You can see that the Native storage SYSUDTLIB. ST_Geometry storage is used.

About Teradata users

Dbc: After installation, it contains all the available space, which is the same as the sys user permission in oracle. Therefore, it is not used for routine management. Sys_Calendar: stores system calendars and views. SysAdmin: includes some system tables and tables required to restart FastLoad under the Network Connection Client. SystemFE: used to generate diagnostic reports for original factory engineers. The default password is service. Crashdumps: stores the pdump, which is allocated from the dbc. At least three crashdump instances are recommended. PUBLIC, EXTUSER, TDPUSER: PUBLIC, and EXTUSER are used by system software. TDPUSER supports two-phase submission. Default, All: used for system software. SYSLIB, SYSUDTLIB: SYSLIB is used to store udfs, and SYSUDTLIB is used to store UDDT.

4: an error is reported during execution.

The system prompts that you do not have the required permissions.

About ArcGIS and Teradata permissions, let's look at the help: http://resources.arcgis.com/en/help/main/10.2/index.html#/User_privileges_for_using_ArcGIS_with_Teradata/019v0000001v000000/

Therefore, we need to grant the above permissions to the sde user. The procedure is as follows:

1: bteq (equivalent to Oracle sqlplus)

2:. logon 127.0.0.1/dbc (the user who connects to the local server)

3: Enter the dbc Password

------------------------------------

The above steps are equivalent to sqlplus sys/as sysdba in Oracle.

TDExpress14.0_Sles10:~ # bteq Teradata BTEQ 14.00.00.02 for LINUX. Enter your logon or BTEQ command:.logon 127.0.0.1/dbc.logon 127.0.0.1/dbcPassword: *** Logon successfully completed. *** Teradata Database Release is 14.00.00.01 *** Teradata Database Version is 14.00.00.01 *** Transaction Semantics are BTET. *** Session Character Set Name is 'ASCII'. *** Total elapsed time was 1 second. BTEQ -- Enter your SQL request or BTEQ command:grant select on sysspatial.spatial_ref_sys to sde with grant option;grant select on sysspatial.spatial_ref_sys to sde with grant option; *** Grant accepted. *** Total elapsed time was 1 second. BTEQ -- Enter your SQL request or BTEQ command:grant select on sysspatial.geometry_columns to sde with grant option;grant select on sysspatial.geometry_columns to sde with grant option; *** Grant accepted. *** Total elapsed time was 1 second. BTEQ -- Enter your SQL request or BTEQ command:grant select on dbc.tvm to sde with grant option;grant select on dbc.tvm to sde with grant option; *** Grant accepted. *** Total elapsed time was 1 second. BTEQ -- Enter your SQL request or BTEQ command:grant udtusage on sysudtlib to sde with grant option;grant udtusage on sysudtlib to sde with grant option; *** Grant accepted. *** Total elapsed time was 1 second. BTEQ -- Enter your SQL request or BTEQ command:grant insert on sysspatial.spatial_ref_sys to sde with grant option;grant insert on sysspatial.spatial_ref_sys to sde with grant option; *** Grant accepted. *** Total elapsed time was 1 second. BTEQ -- Enter your SQL request or BTEQ command:grant update on sysspatial.geometry_columns to sde with grant option;grant update on sysspatial.geometry_columns to sde with grant option; *** Grant accepted. *** Total elapsed time was 1 second. BTEQ -- Enter your SQL request or BTEQ command:grant execute function on sysspatial to sde with grant option;grant execute function on sysspatial to sde with grant option; *** Grant accepted. *** Total elapsed time was 1 second. BTEQ -- Enter your SQL request or BTEQ command:grant execute procedure on sysspatial to sde with grant option;grant execute procedure on sysspatial to sde with grant option; *** Grant accepted. *** Total elapsed time was 1 second.

5. re-import the data.

Executing: FeatureClassToFeatureClass "C:\Program Files\ArcGIS\Desktop10.2\ArcGlobeData\continent.shp" "Database Connections\Connection to teradata.sde" con # "CONTINENT "CONTINENT" true true false 13 Text 0 0 ,First,#,C:\Program Files\ArcGIS\Desktop10.2\ArcGlobeData\continent.shp,CONTINENT,-1,-1" #Start Time: Mon Jul 15 15:34:48 2013Succeeded at Mon Jul 15 15:39:49 2013 (Elapsed Time: 5 minutes 1 seconds)

When ArcGIS uses Teradata to store spatial data, the spatial field of spatial data must be Shape or the alias of the view is Shape. In fact, the underlying storage of ST_geometry of Teradata is CLOB.

You must use the tessellation function provided by Teradata to create and maintain the spatial index information for the ST_geometry column of Teradata. This tessellation spatial index is mainly used for pre-defined spatial range areas and cannot be used by ArcGIS Spatial operations (Dynamic Input similar to zoom in, zoom out, and roaming ), however, the tessellation index can be applied to querylayer.

The ST_geometry of teradata only supports two-dimensional data and does not support data protection of M and Z.

Use ArcGIS to import the element class to the Teradata database. If the spatial projection information of the element class is not stored in the system table of teradata, arcGIS adds related records to the spatial_ref_sys and geometry_columns tables of Teradata. However, if the Teradata version is updated, the tables are deleted, recreated, and the default values are restored, therefore, the new projection record needs to be input again.

Before upgrading teradata, you need to back up the two tables.
Specific operation information:

Http://resources.arcgis.com/en/help/main/10.2/index.html#/Teradata_and_ArcGIS/019v0000001t000000/

From the above description, I also did related tests, moved a bit, and waited for a long time. You can see the blank area above, the Drawing in the lower left corner... and the small Earth in the lower-right corner can prove that this also verifies Esri's help description, because Teradata is a data warehouse, and it is not recommended that you display the view on ArcMap. In addition, the ArcGIS operation described above does not apply to spatial indexes.

========================================================== ======================================

Use the Teradata Client

In fact, users can also download the Teradata client to learn more about ArcGIS's storage in Teradata. The vmvm also comes with a Teradata Studio Express.

Http://downloads.teradata.com/download/tools/teradata-studio

In fact, there is a better Teradata SQL Assistant, but I have not downloaded it

After the installation, we connect to the dbc user to view

Because these breads contain many vertices, they do not list all information completely.

select * from con; *** Query completed. 8 rows found. 3 columns returned. *** Total elapsed time was 3 seconds.   OBJECTID Shape----------- ---------------------------------------------------------------          7 MULTIPOLYGON (((151.540252689999988 -24.04583359,151.54656982 -          5 MULTIPOLYGON (((-39.66893005 -18.32560348,-39.65861511 -18.2786          3 MULTIPOLYGON (((10.9794426 54.3805542,10.94645691 54.38451004,1          1 MULTIPOLYGON (((57.2022171 25.99166107,57.23068237 25.98513222,          8 MULTIPOLYGON (((163.766113280000013 -82.0816803,163.75585937 -8          6 MULTIPOLYGON (((171.1852417 -44.9383316,171.197189329999986 -44          4 MULTIPOLYGON (((0.69465101 5.77336502,0.68798602 5.7538538,0.78          2 MULTIPOLYGON (((-61.69083405 56.54804993,-61.71340179 56.569091

The above is a simple study of ArcGIS10.2 and Teradata. Due to the first contact with Teradata, many commands are not very familiar with it. The above section does not analyze the large data volume, this is just an attempt of first-time integration.

References:

[1]: http://blog.r-tree.net/

Bytes -------------------------------------------------------------------------------------------------------

Bytes ------------------------------------------------------------------------------------------------------