{"id":7983,"date":"2018-02-14T04:16:24","date_gmt":"2018-02-14T04:16:24","guid":{"rendered":"https:\/\/data-flair.training\/blogs\/?p=7983"},"modified":"2021-12-07T12:06:38","modified_gmt":"2021-12-07T06:36:38","slug":"sqoop-hcatalog-integration","status":"publish","type":"post","link":"https:\/\/data-flair.training\/blogs\/sqoop-hcatalog-integration\/","title":{"rendered":"Sqoop HCatalog Integration"},"content":{"rendered":"

In Sqoop<\/strong><\/a>, there is a table and storage management service for Hadoop<\/strong><\/a> which enables users with different data processing tools to more easily read and write data on the grid. That tool what we call is Sqoop HCatalog. So, in this article, we will learn the whole concept of Sqoop HCatalog Integration.<\/span><\/p>\n

Introduction to Sqoop HCatalog<\/h3>\n

In Sqoop, there is a table and storage management service for Hadoop which enables users with different data processing tools to more easily read and write data on the grid. That tool what we call is Sqoop HCatalog. In the Hadoop distributed file system (HDFS<\/strong><\/a>) HCatalog\u2019s table abstraction presents users with a relational view of data. <\/span><\/p>\n

Also, ensures that users need not worry about where or in what format their data is stored. Like in RCFile format, text files, or SequenceFiles.<\/span><\/p>\n

In addition, it supports reading as well as writing files in any format for which a Hive<\/strong><\/a> SerDe (serializer-deserializer) is present. Also, it supports RCFile, CSV, JSON, and SequenceFile formats. However, we must provide the InputFormat and OutputFormat as well as the SerDe, to use a custom format.<\/span><\/p>\n

Moreover, it can abstract various storage formats which used in providing the RCFile (and future file types) support to Sqoop.<\/span><\/p>\n

Follow this link to know more about Sqoop Supported Databases<\/a>\u00a0<\/strong>& Sqoop Validation<\/strong><\/a><\/p>\n

Exposing Sqoop HCatalog Tables<\/h3>\n

Basically, with Sqoop it is patterned on an existing feature set which supports Avro and Hive tables. Moreover, it introduces\u00a0seven new command line options. Also, some command line options defined for Hive can\u00a0reuse.<\/span>
\nLet’s revise Sqoop Features<\/strong><\/a><\/p>\n

a. New Command Line Options<\/h4>\n

–hcatalog-database
\nIt specifies the database name for the Sqoop HCatalog table. Moreover, we use\u00a0the default database name, if not specified. However, without –hcatalog-table offering the –hcatalog-database option is an error.\u00a0 <\/span>
\n–hcatalog-table
\nThe argument value for this option is the HCatalog tablename.<\/span>
\n–hcatalog-home
\nThe home directory for the Sqoop HCatalog installation. Basically, with necessary HCatalog libraries, the directory must\u00a0have a lib subdirectory and a share\/hcatalog subdirectory.\u00a0<\/span>
\n–create-hcatalog-table
\nWhen importing data<\/strong><\/a> it specifies Sqoop HCatalog table should be created automatically. However, HCatalog tables are assumed to exist, by default.<\/span>
\n–hcatalog-storage-stanza
\nIt specifies the storage stanza to be appended to the table.\u00a0<\/span>
\n—<\/strong>hcatalog–<\/strong>partition-keys and —<\/strong>hcatalog–<\/strong>partition-values
\nTo specify multiple static partition key\/value pairs we use these two options.<\/p>\n

b. Supported Sqoop Hive Options<\/h4>\n

To provide additional input to the HCatalog jobs we can use the following Sqoop options along with the –hcatalog-table option. Also, we can reuse some of the existing Hive import job options with HCatalog jobs despite creating Sqoop-HCatalog-specific options for the same purpose.<\/span>
\n–map-column-hive
\nTo HCatalog with a specific HCatalog type, this option maps a database column.<\/span>
\n–hive-home
\nIt is the Hive home location.<\/span>
\n–hive-partition-key
\nBasically, we use it for static partitioning filter. However, the partitioning key should be of type STRING. Although, there can be only one static partitioning key.<\/span>
\n–hive-partition-value
\nHere, all the value are associated with the partition. <\/span><\/p>\n

c. Direct Mode support<\/h4>\n

Sqoop HCatalog integration has been enhanced to support direct mode connectors. Basically, those are high-performance connectors specific to a database. Moreover, to take advantage of this feature it is must enhance Netezza direct mode connector.<\/span>
\nNote:<\/b> Only Netezza direct mode connector is currently ready\u00a0to work with HCatalog.<\/span>
\nFollow this link to know more about Sqoop Connectors & Drivers\u00a0<\/strong><\/a><\/p>\n

d. Unsupported Sqoop Options<\/h4>\n

i. Unsupported Sqoop Hive Import Options<\/span><\/h4>\n

HCatalog jobs do not support following Sqoop Hive import options. Such as:<\/span>
\n–hive-import
\n–hive-overwrite<\/p>\n

ii. Unsupported Sqoop Export and Import Options<\/span><\/h4>\n

HCatalog job does not support following Sqoop export<\/strong><\/a> and import options. Such as:<\/span>
\n–export-dir
\n–target-dir
\n–warehouse-dir
\n–append
\n–as-sequencefile
\n–as-avrodatafile
\n–as-parquetfile<\/p>\n

e. Ignored Sqoop Options<\/h4>\n

Basically, HCatalog jobs ignore the following options. Such as:<\/span><\/p>\n

    \n
  1. Basically, it ignores all input delimiter options.<\/span><\/li>\n
  2. Unless we use –hive-drop-import-delims or –hive-delims-replacement, it generally ignores output delimiters.<\/span><\/li>\n<\/ol>\n

    Automatic Table Creation in Sqoop<\/h3>\n

    While importing into Hadoop, Sqoop manages and create the table metadata. Also, it offers this feature with the option –create-hcatalog-table. Moreover, it offers storage agnosticism to Sqoop data movement jobs. Basically, for that feature, HCatalog import jobs lets a user specify the storage format for the created table.<\/span><\/p>\n

    Although we use –create-hcatalog-table option as an indicator that a table has to be created as part of the HCatalog import job. Although, the table creation will fail and the job will be aborted if the option –create-hcatalog-table is specified and the table exists.<\/span><\/p>\n

    However, if the option –hive-partition-key is specified, hence the value of this option is used as the partitioning key for the newly created table. Basically, \u00a0with this option, only one partitioning key can be specified.<\/span><\/p>\n

    Moreover, when mapped to an HCatalog table object names are mapped to the lowercase equivalents. It includes the table name as well as field names.<\/span><\/p>\n

    Follow this link to know about Sqoop Architecture<\/b><\/a><\/p>\n

    Delimited Text Formats – Field and Line Delimiter Characters<\/h3>\n

    Basically, it supports delimited text format as one of the table storage formats. However, while the delimited text is already used also the imported data has fields which contain those delimiters, So the data may be parsed into a different number of fields and records by Hive, thereby losing data fidelity.<\/span><\/p>\n

    We can use any of these existing Sqoop import options, for this case:<\/span>
    \n–hive-delims-replacement
    \n–hive-drop-import-delims<\/p>\n

    Note that any column of type STRING will be formatted with the Hive delimiter processing if either of these options is provided for import. Afterwards, written to the HCatalog table.<\/span><\/p>\n

    Sqoop HCatalog Table Requirements<\/h3>\n

    Make sure, if the default table creation options are not enough then it is must that we create Sqoop-HCatalog table before using it as part of a Sqoop job<\/a><\/strong>. Moreover, we can use all storage formats which Hcatalog supports the creation of the HCatalog tables. Basically, this makes this feature readily adopt new storage formats that come into the Hive project. For example, ORC files.<\/span><\/p>\n

    Support for Partitioning<\/h3>\n

    There are several table types which Sqoop HCatalog feature supports such as:<\/span><\/p>\n

      \n
    1. Unpartitioned tables<\/span><\/li>\n
    2. Partitioned tables with a static partitioning key specified<\/span><\/li>\n
    3. Also, Partitioned tables with dynamic partition keys from the database result set<\/span><\/li>\n
    4. Partitioned tables with a combination of a static key and additional dynamic partitioning keys<\/span><\/li>\n<\/ol>\n

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

      Basically, it supports all the Hive types which are part of Hive 0.13 version. Although it does not support complex HCatalog types.<\/span>
      \nHowever, for imports it only supports, BLOB\/CLOB database types.<\/span><\/p>\n

      To know about\u00a0Sqoop list Databases<\/strong> <\/a>& Sqoop list Tables<\/a>\u00a0<\/strong>follow these link<\/p>\n

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

      Before launching the Sqoop HCatalog jobs, the environment variable HADOOP_CLASSPATH must set, to run HCatalog jobs. Like below.<\/span>
      \nHADOOP_CLASSPATH=$(hcat -classpath) export HADOOP_CLASSPATH
      \nAlso, By the Sqoop job, all the essential HCatalog dependencies will be copied to the distributed cache automatically.<\/span><\/p>\n

      Examples of Sqoop HCatalog<\/h3>\n

      Now, Create an HCatalog table, like:<\/span>
      \nhcat -e “create table txn(txn_date string, cust_id string, amount float, store_id int) partitioned by (cust_id string) stored as rcfile;”
      \nAfterwards, it invokes import and export of the “txn” HCatalog table. Like below:<\/span>
      \nImport:<\/strong>
      \n$SQOOP_HOME\/bin\/sqoop import –connect <jdbc-url> -table <table-name> –hcatalog-table txn <other sqoop options>
      \nExport:<\/strong>
      \n$SQOOP_HOME\/bin\/sqoop export –connect <jdbc-url> -table <table-name> –hcatalog-table txn <other sqoop options><\/p>\n

      Conclusion<\/h3>\n

      So, in this Sqoop article, we have learned the whole concept of\u00a0 Sqoop HCatalog Integration. Moreover, we have covered each and every aspect regarding Sqoop HCatalog. Still, if any doubt occurs regarding Sqoop HCatalog, please ask through the comment section.<\/span>
      \nSee Also-       Features of Sqoop<\/a><\/strong> & Sqoop troubleshooting Tips<\/strong><\/a>
      \n      For reference<\/strong><\/a>

      In Sqoop, there is a table and storage management service for Hadoop which enables users with different data processing tools to more easily read and write data on the grid. That tool what we...<\/p>\n","protected":false},"author":9,"featured_media":8191,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[68],"tags":[5518,13622,13629,13689,13980],"class_list":["post-7983","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sqoop","tag-hcatalog-integration-in-sqoop","tag-sqoop-export","tag-sqoop-import","tag-sqoop-hcatalog-integration","tag-support-for-partitioning"],"yoast_head":"\nSqoop HCatalog Integration - DataFlair<\/title>\n<meta name=\"description\" content=\"Sqoop HCatalog Integration example- Exposing Hcatalog Table,Automatic table creation & requirement,Delimited Text Formats,Field and Line Delimiter Character\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/data-flair.training\/blogs\/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 - DataFlair\" \/>\n<meta property=\"og:description\" content=\"Sqoop HCatalog Integration example- Exposing Hcatalog Table,Automatic table creation & requirement,Delimited Text Formats,Field and Line Delimiter Character\" \/>\n<meta property=\"og:url\" content=\"https:\/\/data-flair.training\/blogs\/sqoop-hcatalog-integration\/\" \/>\n<meta property=\"og:site_name\" content=\"DataFlair\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/DataFlairWS\/\" \/>\n<meta property=\"article:published_time\" content=\"2018-02-14T04:16:24+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-12-07T06:36:38+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/02\/Introduction-to-Sqoop-HCatalog-Integration-01-1.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=\"DataFlair Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@DataFlairWS\" \/>\n<meta name=\"twitter:site\" content=\"@DataFlairWS\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"DataFlair 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 - DataFlair","description":"Sqoop HCatalog Integration example- Exposing Hcatalog Table,Automatic table creation & requirement,Delimited Text Formats,Field and Line Delimiter Character","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:\/\/data-flair.training\/blogs\/sqoop-hcatalog-integration\/","og_locale":"en_US","og_type":"article","og_title":"Sqoop HCatalog Integration - DataFlair","og_description":"Sqoop HCatalog Integration example- Exposing Hcatalog Table,Automatic table creation & requirement,Delimited Text Formats,Field and Line Delimiter Character","og_url":"https:\/\/data-flair.training\/blogs\/sqoop-hcatalog-integration\/","og_site_name":"DataFlair","article_publisher":"https:\/\/www.facebook.com\/DataFlairWS\/","article_published_time":"2018-02-14T04:16:24+00:00","article_modified_time":"2021-12-07T06:36:38+00:00","og_image":[{"width":1200,"height":628,"url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/02\/Introduction-to-Sqoop-HCatalog-Integration-01-1.jpg","type":"image\/jpeg"}],"author":"DataFlair Team","twitter_card":"summary_large_image","twitter_creator":"@DataFlairWS","twitter_site":"@DataFlairWS","twitter_misc":{"Written by":"DataFlair Team","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/data-flair.training\/blogs\/sqoop-hcatalog-integration\/#article","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/sqoop-hcatalog-integration\/"},"author":{"name":"DataFlair Team","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/6e11a88eea98179e861a0933f8959f8f"},"headline":"Sqoop HCatalog Integration","datePublished":"2018-02-14T04:16:24+00:00","dateModified":"2021-12-07T06:36:38+00:00","mainEntityOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/sqoop-hcatalog-integration\/"},"wordCount":1274,"commentCount":0,"publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/sqoop-hcatalog-integration\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/02\/Introduction-to-Sqoop-HCatalog-Integration-01-1.jpg","keywords":["HCatalog Integration in Sqoop","sqoop export","sqoop import","Sqoop-HCatalog Integration","Support for Partitioning"],"articleSection":["Sqoop Tutorials"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/data-flair.training\/blogs\/sqoop-hcatalog-integration\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/data-flair.training\/blogs\/sqoop-hcatalog-integration\/","url":"https:\/\/data-flair.training\/blogs\/sqoop-hcatalog-integration\/","name":"Sqoop HCatalog Integration - DataFlair","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/#website"},"primaryImageOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/sqoop-hcatalog-integration\/#primaryimage"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/sqoop-hcatalog-integration\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/02\/Introduction-to-Sqoop-HCatalog-Integration-01-1.jpg","datePublished":"2018-02-14T04:16:24+00:00","dateModified":"2021-12-07T06:36:38+00:00","description":"Sqoop HCatalog Integration example- Exposing Hcatalog Table,Automatic table creation & requirement,Delimited Text Formats,Field and Line Delimiter Character","breadcrumb":{"@id":"https:\/\/data-flair.training\/blogs\/sqoop-hcatalog-integration\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/data-flair.training\/blogs\/sqoop-hcatalog-integration\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/sqoop-hcatalog-integration\/#primaryimage","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/02\/Introduction-to-Sqoop-HCatalog-Integration-01-1.jpg","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/02\/Introduction-to-Sqoop-HCatalog-Integration-01-1.jpg","width":1200,"height":628,"caption":"What is Sqoop-HCatalog Integration"},{"@type":"BreadcrumbList","@id":"https:\/\/data-flair.training\/blogs\/sqoop-hcatalog-integration\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Blog Home","item":"https:\/\/data-flair.training\/blogs\/"},{"@type":"ListItem","position":2,"name":"Sqoop Tutorials","item":"https:\/\/data-flair.training\/blogs\/category\/sqoop\/"},{"@type":"ListItem","position":3,"name":"Sqoop HCatalog Integration"}]},{"@type":"WebSite","@id":"https:\/\/data-flair.training\/blogs\/#website","url":"https:\/\/data-flair.training\/blogs\/","name":"DataFlair","description":"Learn Today. Lead Tomorrow.","publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/data-flair.training\/blogs\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/data-flair.training\/blogs\/#organization","name":"DataFlair","url":"https:\/\/data-flair.training\/blogs\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/logo\/image\/","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2016\/07\/Data-Flair.png","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2016\/07\/Data-Flair.png","width":106,"height":48,"caption":"DataFlair"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/DataFlairWS\/","https:\/\/x.com\/DataFlairWS","https:\/\/www.linkedin.com\/company\/dataflair-web-services-pvt-ltd\/","https:\/\/www.youtube.com\/user\/DataFlairWS"]},{"@type":"Person","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/6e11a88eea98179e861a0933f8959f8f","name":"DataFlair Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/54363d8635c383dcd7e3be0ef00dbeef4217c45bf323d27bc2c92366b724df08?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/54363d8635c383dcd7e3be0ef00dbeef4217c45bf323d27bc2c92366b724df08?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/54363d8635c383dcd7e3be0ef00dbeef4217c45bf323d27bc2c92366b724df08?s=96&d=mm&r=g","caption":"DataFlair Team"},"description":"At DataFlair, our team of experts is committed to offering high-quality tutorials on programming, Java, Python, C++, DSA, AI, ML, data Science, Android, Flutter, MERN, Web Development, and technology. We simplify learning to help you advance your career in the tech world.","url":"https:\/\/data-flair.training\/blogs\/author\/dfteam5\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/7983","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/users\/9"}],"replies":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/comments?post=7983"}],"version-history":[{"count":7,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/7983\/revisions"}],"predecessor-version":[{"id":105304,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/7983\/revisions\/105304"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media\/8191"}],"wp:attachment":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media?parent=7983"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/categories?post=7983"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/tags?post=7983"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}