{"id":79636,"date":"2020-08-26T09:00:48","date_gmt":"2020-08-26T03:30:48","guid":{"rendered":"https:\/\/techvidvan.com\/tutorials\/?p=79636"},"modified":"2020-08-26T09:00:48","modified_gmt":"2020-08-26T03:30:48","slug":"sqoop-hcatalog-integration","status":"publish","type":"post","link":"https:\/\/techvidvan.com\/tutorials\/sqoop-hcatalog-integration\/","title":{"rendered":"Sqoop HCatalog Integration"},"content":{"rendered":"

In this article, you will explore each and everything related to the Sqoop HCatalog Integration. The article will first provide an introduction to Sqoop HCatalog Integration. Then you will see the new command-line option and various Hive options.<\/p>\n

The article will also enlist some unsupported and ignored Hive and Sqoop options. At last, you will see the examples which show how you can create an HCatlog table and invoke Sqoop import and export.<\/p>\n

Let us first start with an introduction to Sqoop HCatalog Integration.<\/p>\n

\"Sqoop<\/a><\/p>\n

Introduction to Sqoop HCatalog Integration<\/h3>\n

HCatalog is a table and the storage management service for Apache Hadoop, which enables the users with different data processing tools such as Hive, Pig, MapReduce to read and write data on a grid with ease.<\/p>\n

The HCatalog table abstraction provides the relational view of data stored in HDFS to the users. It ensures users not to worry about the location where and the format in which the data is stored. It can store data in any format like RCFile format, text files, or SequenceFiles.<\/p>\n

HCatalog provides support for reading and writing files in any of the formats for which the Hive SerDe, that is, serializer- deserializer has been written.<\/p>\n

HCatalog, by default, HCatalog supports RCFile, CSV, JSON, and SequenceFile formats. For using the custom format, you have to provide the InputFormat, OutputFormat as well as the SerDe.<\/p>\n

Exposing HCatalog tables to Apache Sqoop<\/h3>\n

The integration of HCatalog with Apache Sqoop is patterned on the existing feature set, which supports Hive and Avro tables.<\/p>\n

For exposing HCatalog tables to Apache Sqoop, the five new command-line options were introduced. It also re-uses some existing Apache Hive command-line options.<\/p>\n

Let us now explore the new command-line options.<\/p>\n

New Command-Line Options<\/h3>\n\n\n\n\n\n\n\n\n
Command-line Option<\/b><\/td>\nDescription<\/b><\/td>\n<\/tr>\n
–hcatalog-database<\/span><\/td>\nThis option specifies the name of the database for the HCatalog table. If we do not specify this option, then it will use the default database name, which is \u2018default\u2019. If we provide the –hcatalog-database option without the –hcatalog-table option, then it is an error. This option is not the required option.<\/span><\/td>\n<\/tr>\n
-hcatalog-table<\/span><\/td>\nThe argument specifies the HCatalog tablename. It is the required option for HCatalog jobs. The home directory must have the lib subdirectory and the share\/hcatalog subdirectory with the required HCatalog libraries.\u00a0<\/span><\/td>\n<\/tr>\n
–hcatalog-home<\/span><\/td>\nThis option specifies the home directory for the HCatalog installation. If we don’t specify this option, then it checks the system environment variable, which is HCAT_HOME , and if it fails, then it will check a system property hcatalog.home.\u00a0<\/span><\/p>\n

If we have not set either of these properties, then it will use the default value. The default value is set to \/usr\/lib\/hcatalog.\u00a0<\/span><\/p>\n

It is also not a required option.<\/span><\/td>\n<\/tr>\n

–create-hcatalog-table<\/span><\/td>\nThis option specifies whether the HCatalog table should be created automatically while importing the data.\u00a0<\/span><\/p>\n

HCatalog tables, by default, are assumed to exist.\u00a0<\/span><\/p>\n

The name of the table will be the same as the name of the database translated to lower case.\u00a0<\/span><\/td>\n<\/tr>\n

–hcatalog-storage- stanza<\/span><\/td>\nThis option specifies the storage stanza, which is to be appended to the table.<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Supported Sqoop Hive Options<\/h3>\n

Some of the Sqoop Hive options are also used with the –hcatalog-table option for providing additional input to the HCatalog jobs.<\/p>\n\n\n\n\n\n\n\n
Command-line Option<\/b><\/td>\nDescription<\/b><\/td>\n<\/tr>\n
–map-column-hive<\/span><\/td>\nThis option maps the database column to the HCatalog with the specific HCatalog type.<\/span><\/td>\n<\/tr>\n
–hive-home<\/span><\/td>\nIt specifies the Hive home location.<\/span><\/td>\n<\/tr>\n
–hive-partition-key<\/span><\/td>\nThis option specifies to use static partitioning filters. The type of partitioning key should be STRING.\u00a0<\/span><\/p>\n

Note: There can be only 1 static partitioning key.<\/span><\/td>\n<\/tr>\n

–hive-partition-value<\/span><\/td>\nThis option specifies the value associated with the partition.<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Direct Mode Support<\/h3>\n

HCatalog integration in Apache Sqoop is now enhanced to provide support for the direct mode connectors. The Direct mode connectors are basically the high-performance connectors specific to the particular database.
\nThe Netezza direct mode connector is enhanced to use this feature for HCatalog jobs.<\/p>\n

Let us now learn Unsupported options.<\/p>\n

Unsupported Options<\/h3>\n

1. Some of the sqoop Hive options are not supported with the HCatalog jobs. They are:<\/p>\n

    \n
  • –hive-import<\/li>\n
  • –hive-overwrite<\/li>\n<\/ul>\n

    2. Also, some of the Sqoop export and import options are also not supported with the HCatalog jobs. They are:<\/p>\n

      \n
    • –direct<\/li>\n
    • –export-dir<\/li>\n
    • –target-dir<\/li>\n
    • –warehouse-dir<\/li>\n
    • –append<\/li>\n
    • –as-sequencefile<\/li>\n
    • –as-avrofile<\/li>\n<\/ul>\n

      3. Ignored Sqoop Options: The Sqoop options which are ignored are:<\/p>\n

        \n
      • All input delimiter options.<\/li>\n
      • Output delimiters except –hive-drop-import-delims or –hive-delims-replacement is used.<\/li>\n<\/ul>\n

        Automatic Table Creation<\/h3>\n

        Sqoop manages and creates the table metadata while importing tables into Apache Hadoop. The HCatalog import jobs also provide support for this feature with an option –create-hcatalog-table. <\/strong><\/p>\n

        One of the major advantages of HCatalog integration is to provide storage agnosticism to Apache Sqoop data movement jobs. For providing this feature, HCatalog imports the jobs provided by specifying an option that lets the user specify the storage format for the table created.<\/p>\n

        We can use the option –hcatalog-storage-stanza<\/strong> for specifying the storage format of the table created newly.
        \nBy default, the value of this option is set to stored as rcfile.<\/p>\n

        If we specify the option –hive-partition-key<\/strong>, then the value of this argument is used as the partitioning key for a newly created table. We can specify only one partitioning key with this option.<\/p>\n

        HCatalog Table Requirements<\/h3>\n

        If the default table creation arguments with the optional storage stanza are not enough, then we should create the HCatalog table before using it as a part of the Sqoop job. We can use all the storage formats supported by HCatalog with the creation of the HCatalog tables.<\/p>\n

        Support for Partitioning<\/h3>\n

        The Sqoop HCatalog integration feature provides support for the following table types:<\/p>\n

          \n
        • Partitioned tables with the static partitioning key specified<\/li>\n
        • Unpartitioned tables<\/li>\n
        • Partitioned tables with the dynamic partition keys from the database result set<\/li>\n
        • Partitioned tables with the combination of the static key and the additional dynamic partitioning keys<\/li>\n<\/ul>\n

          Support for HCatalog Data Types<\/h3>\n

          It provides support for all the primitive HCatalog types. At present, all the complex HCatalog types are not supported.<\/p>\n

          The BLOB or CLOB<\/strong> database types are only supported for the imports.<\/p>\n

          Providing Hive and HCatalog Libraries for the Sqoop Job<\/strong><\/p>\n

          In Sqoop HCatalog integration, any HCatalog job depends on the set of jar files being available on the Sqoop client host as well as on the node where the Map\/Reduce tasks run.<\/p>\n

          For running the HCatalog jobs, the environment variable, that is, HADOOP_CLASSPATH<\/strong> must be set up before launching the Sqoop HCatalog jobs.<\/p>\n

          HADOOP_CLASSPATH=$(hcat -classpath) export HADOOP_CLASSPATH<\/pre>\n
          The Sqoop will copy the required HCatalog dependencies to the distributed cache automatically.<\/p>\n
          Example Invocations<\/h3>\n
          1:<\/strong> We can create an HCatalog table as:<\/p>\n
          hcat -e \"create table emp(emp_name string, emp_id string, emp_salary float, dept_id int) partitioned by (emp_id string) stored as rcfile;\"\n<\/pre>\n
          2:<\/strong> We can invoke the Sqoop import of the “emp” HCatalog table as follows:<\/p>\n
          $SQOOP_HOME\/bin\/sqoop import --connect <jdbc-url> -table <table-name> --hcatalog-table emp <other sqoop options>\n<\/pre>\n
          3:<\/strong> We can invoke the Sqoop export of the “emp” HCatalog table as follows:<\/p>\n
          $SQOOP_HOME\/bin\/sqoop export --connect <jdbc-url> -table <table-name> --hcatalog-table emp <other sqoop options>\n<\/pre>\n
          Summary<\/h3>\n
          I hope after reading this article, you clearly understand the Sqoop HCatalog Integration. Now you can create HCatalog tables and invoke Sqoop import and export jobs on them.<\/p>\n
          If you have any queries regarding Sqoop HCatalog Integration, then do share in the comment section.<\/p>\n","protected":false},"excerpt":{"rendered":"
          In this article, you will explore each and everything related to the Sqoop HCatalog Integration. The article will first provide an introduction to Sqoop HCatalog Integration. Then you will see the new command-line option...<\/p>\n","protected":false},"author":1,"featured_media":79685,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3163],"tags":[3197,3198],"class_list":["post-79636","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sqoop","tag-sqoop-hcatalog-integration","tag-what-is-sqoop-hcatalog-integration"],"yoast_head":"\nSqoop HCatalog Integration - TechVidvan<\/title>\n<meta name=\"description\" content=\"HCatalog integration in Sqoop has been enhanced to support direct mode connectors. These are high performance connectors specific to a database.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/techvidvan.com\/tutorials\/sqoop-hcatalog-integration\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Sqoop HCatalog Integration - TechVidvan\" \/>\n<meta property=\"og:description\" content=\"HCatalog integration in Sqoop has been enhanced to support direct mode connectors. These are high performance connectors specific to a database.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/techvidvan.com\/tutorials\/sqoop-hcatalog-integration\/\" \/>\n<meta property=\"og:site_name\" content=\"TechVidvan\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/TechVidvan\/\" \/>\n<meta property=\"article:published_time\" content=\"2020-08-26T03:30:48+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/techvidvan.com\/tutorials\/wp-content\/uploads\/2020\/08\/Sqoop-HCatalog-Integration-TV.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1200\" \/>\n\t<meta property=\"og:image:height\" content=\"628\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"TechVidvan Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@vidvantech\" \/>\n<meta name=\"twitter:site\" content=\"@vidvantech\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"TechVidvan Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"6 minutes\" \/>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Sqoop HCatalog Integration - TechVidvan","description":"HCatalog integration in Sqoop has been enhanced to support direct mode connectors. These are high performance connectors specific to a database.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/techvidvan.com\/tutorials\/sqoop-hcatalog-integration\/","og_locale":"en_US","og_type":"article","og_title":"Sqoop HCatalog Integration - TechVidvan","og_description":"HCatalog integration in Sqoop has been enhanced to support direct mode connectors. These are high performance connectors specific to a database.","og_url":"https:\/\/techvidvan.com\/tutorials\/sqoop-hcatalog-integration\/","og_site_name":"TechVidvan","article_publisher":"https:\/\/www.facebook.com\/TechVidvan\/","article_published_time":"2020-08-26T03:30:48+00:00","og_image":[{"width":1200,"height":628,"url":"https:\/\/techvidvan.com\/tutorials\/wp-content\/uploads\/2020\/08\/Sqoop-HCatalog-Integration-TV.jpg","type":"image\/jpeg"}],"author":"TechVidvan Team","twitter_card":"summary_large_image","twitter_creator":"@vidvantech","twitter_site":"@vidvantech","twitter_misc":{"Written by":"TechVidvan Team","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/techvidvan.com\/tutorials\/sqoop-hcatalog-integration\/#article","isPartOf":{"@id":"https:\/\/techvidvan.com\/tutorials\/sqoop-hcatalog-integration\/"},"author":{"name":"TechVidvan Team","@id":"https:\/\/techvidvan.com\/tutorials\/#\/schema\/person\/e9c26e74dd3d87421f7ada9433b8cd22"},"headline":"Sqoop HCatalog Integration","datePublished":"2020-08-26T03:30:48+00:00","mainEntityOfPage":{"@id":"https:\/\/techvidvan.com\/tutorials\/sqoop-hcatalog-integration\/"},"wordCount":1149,"commentCount":0,"publisher":{"@id":"https:\/\/techvidvan.com\/tutorials\/#organization"},"image":{"@id":"https:\/\/techvidvan.com\/tutorials\/sqoop-hcatalog-integration\/#primaryimage"},"thumbnailUrl":"https:\/\/techvidvan.com\/tutorials\/wp-content\/uploads\/2020\/08\/Sqoop-HCatalog-Integration-TV.jpg","keywords":["Sqoop-HCatalog Integration","What is Sqoop HCatalog Integration"],"articleSection":["Sqoop Tutorials"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/techvidvan.com\/tutorials\/sqoop-hcatalog-integration\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/techvidvan.com\/tutorials\/sqoop-hcatalog-integration\/","url":"https:\/\/techvidvan.com\/tutorials\/sqoop-hcatalog-integration\/","name":"Sqoop HCatalog Integration - TechVidvan","isPartOf":{"@id":"https:\/\/techvidvan.com\/tutorials\/#website"},"primaryImageOfPage":{"@id":"https:\/\/techvidvan.com\/tutorials\/sqoop-hcatalog-integration\/#primaryimage"},"image":{"@id":"https:\/\/techvidvan.com\/tutorials\/sqoop-hcatalog-integration\/#primaryimage"},"thumbnailUrl":"https:\/\/techvidvan.com\/tutorials\/wp-content\/uploads\/2020\/08\/Sqoop-HCatalog-Integration-TV.jpg","datePublished":"2020-08-26T03:30:48+00:00","description":"HCatalog integration in Sqoop has been enhanced to support direct mode connectors. These are high performance connectors specific to a database.","breadcrumb":{"@id":"https:\/\/techvidvan.com\/tutorials\/sqoop-hcatalog-integration\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/techvidvan.com\/tutorials\/sqoop-hcatalog-integration\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/techvidvan.com\/tutorials\/sqoop-hcatalog-integration\/#primaryimage","url":"https:\/\/techvidvan.com\/tutorials\/wp-content\/uploads\/2020\/08\/Sqoop-HCatalog-Integration-TV.jpg","contentUrl":"https:\/\/techvidvan.com\/tutorials\/wp-content\/uploads\/2020\/08\/Sqoop-HCatalog-Integration-TV.jpg","width":1200,"height":628,"caption":"Sqoop HCatalog Integration"},{"@type":"BreadcrumbList","@id":"https:\/\/techvidvan.com\/tutorials\/sqoop-hcatalog-integration\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/techvidvan.com\/tutorials\/"},{"@type":"ListItem","position":2,"name":"Sqoop HCatalog Integration"}]},{"@type":"WebSite","@id":"https:\/\/techvidvan.com\/tutorials\/#website","url":"https:\/\/techvidvan.com\/tutorials\/","name":"TechVidvan Blogs","description":"","publisher":{"@id":"https:\/\/techvidvan.com\/tutorials\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/techvidvan.com\/tutorials\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/techvidvan.com\/tutorials\/#organization","name":"TechVidvan","url":"https:\/\/techvidvan.com\/tutorials\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/techvidvan.com\/tutorials\/#\/schema\/logo\/image\/","url":"https:\/\/techvidvan.com\/tutorials\/wp-content\/uploads\/2024\/03\/techvidvan-logo-200x50-1.webp","contentUrl":"https:\/\/techvidvan.com\/tutorials\/wp-content\/uploads\/2024\/03\/techvidvan-logo-200x50-1.webp","width":200,"height":50,"caption":"TechVidvan"},"image":{"@id":"https:\/\/techvidvan.com\/tutorials\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/TechVidvan\/","https:\/\/x.com\/vidvantech"]},{"@type":"Person","@id":"https:\/\/techvidvan.com\/tutorials\/#\/schema\/person\/e9c26e74dd3d87421f7ada9433b8cd22","name":"TechVidvan Team","description":"The TechVidvan Team delivers practical, beginner-friendly tutorials on programming, Java, Python, C++, DSA, AI, ML, data Science, Android, Flutter, MERN, Web Development, and technology. Our experts are here to help you upskill and excel in today\u2019s tech industry."}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/techvidvan.com\/tutorials\/wp-json\/wp\/v2\/posts\/79636","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/techvidvan.com\/tutorials\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/techvidvan.com\/tutorials\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/techvidvan.com\/tutorials\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/techvidvan.com\/tutorials\/wp-json\/wp\/v2\/comments?post=79636"}],"version-history":[{"count":0,"href":"https:\/\/techvidvan.com\/tutorials\/wp-json\/wp\/v2\/posts\/79636\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/techvidvan.com\/tutorials\/wp-json\/wp\/v2\/media\/79685"}],"wp:attachment":[{"href":"https:\/\/techvidvan.com\/tutorials\/wp-json\/wp\/v2\/media?parent=79636"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/techvidvan.com\/tutorials\/wp-json\/wp\/v2\/categories?post=79636"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/techvidvan.com\/tutorials\/wp-json\/wp\/v2\/tags?post=79636"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}