Spark JDBC reader is capable of reading data in parallel by splitting it into several partitions. There are four options.  partitionColumn is the name of the column used for partitioning. An important condition is that the column must be numeric (integer or decimal), date or timestamp type. If the partitionColumn parameter is not specified, Spark will use a single executor and create one non-empty partition. Reading data will not be distributed or parallelized. numPartitions is the maximum number of partitions that can be used for simultaneous table reading and writing. The lowerBound and upperBound boundaries used to define the partition width. These boundaries determines how many rows from a given range of partition column values can be within a single partition. For Example -    df = spark.read \ .format("jdbc") \ .option("url", "jdbc:postgresql:postgres") \ .option("dbtable", "db.table") \ .option("user", "user")\ .option("password", "pass") \ .option("numPartitions", "10") \ .option("lowerBound", "100") \ .option("upperBound", "1100") \ .load()   This method will use the upper and lower bounds, and number of partitions to create where clauses. For example, if lower bound is set to 100, upper bound is 1,000, and number of partitions is 10, then the number of rows read by each task, called stride in the reference documentation, will be: (upper bound - lower bound) / number of partitions (1100 - 100) / 10 = 100 And the series of filters applied to each task will be: where partitionColumn < 100 where partitionColumn >= 100 and partitionColumn < 200 where partitionColumn >= 200 and partitionColumn < 300 ... where partitionColumn >= 1100   The lowerBound and upperBound define partitioning boundaries, but they DO NOT participate in filtering rows of the table. Therefore, Spark partitions and returns ALL the rows of the table. It is important to note that all data will be read whether partitioning is used or not. For example suppose we have partitionColumn data range in [0, 10000] and we set numPartitions=10, lowerBound=4000 and upperBound=5000. As shown in the illustration above, the first and last partitions will contain all the data outside of the corresponding upper or lower boundary. Another example, suppose we have partitionColumn data range in [2000, 4000] and we set numPartitions=10, lowerBound=0 and upperBound=10000. In this case, then only 2 of the 10 queries (one for each partition) will do all the work, not ideal. In this scenario, the best configuration would be numPartitions=10, lowerBound=2000, upperBound=4000   ... View more

use  showHeader=false and outputformat as csv2 to extract only count value to bash variable.  [root@nightly-cm-ap-2 ~]# count=$(beeline -u "<jdbcURL>" --showHeader=false --outputformat=csv2 -e "select count(*) from sample_07") SLF4J: Class path contains multiple SLF4J bindings. . . . INFO : Compiling command(queryId=hive_20230714065303_399f60fb-d947-4fcf-b017-c64a4a39251f): select count(*) from sample_07 INFO : Semantic Analysis Completed (retrial = false) INFO : Created Hive schema: Schema(fieldSchemas:[FieldSchema(name:_c0, type:bigint, comment:null)], properties:null) INFO : Completed compiling command(queryId=hive_20230714065303_399f60fb-d947-4fcf-b017-c64a4a39251f); Time taken: 0.213 seconds INFO : Executing command(queryId=hive_20230714065303_399f60fb-d947-4fcf-b017-c64a4a39251f): select count(*) from sample_07 INFO : Completed executing command(queryId=hive_20230714065303_399f60fb-d947-4fcf-b017-c64a4a39251f); Time taken: 0.009 seconds INFO : OK 1 row selected (0.675 seconds) [root@nightly-cm-ap-2 ~]# echo $count 822 [root@nightly-cm-ap-2 ~]#   For additional information use beeline --help. [root@nightly-cm-ap-2 ~]# beeline --help SLF4J: Class path contains multiple SLF4J bindings. . . . Usage: java org.apache.hive.cli.beeline.BeeLine -u <database url> the JDBC URL to connect to -c <named url> the named JDBC URL to connect to, which should be present in beeline-site.xml as the value of beeline.hs2.jdbc.url.<namedUrl> -r reconnect to last saved connect url (in conjunction with !save) -n <username> the username to connect as -p <password> the password to connect as -d <driver class> the driver class to use -i <init file> script file for initialization -e <query> query that should be executed -f <exec file> script file that should be executed -w (or) --password-file <password file> the password file to read password from --hiveconf property=value Use value for given property --hivevar name=value hive variable name and value This is Hive specific settings in which variables can be set at session level and referenced in Hive commands or queries. --property-file=<property-file> the file to read connection properties (url, driver, user, password) from --color=[true/false] control whether color is used for display --showHeader=[true/false] show column names in query results --escapeCRLF=[true/false] show carriage return and line feeds in query results as escaped \r and \n --headerInterval=ROWS; the interval between which heades are displayed --fastConnect=[true/false] skip building table/column list for tab-completion --autoCommit=[true/false] enable/disable automatic transaction commit --verbose=[true/false] show verbose error messages and debug info --showWarnings=[true/false] display connection warnings --showDbInPrompt=[true/false] display the current database name in the prompt --showNestedErrs=[true/false] display nested errors --numberFormat=[pattern] format numbers using DecimalFormat pattern --force=[true/false] continue running script even after errors --maxWidth=MAXWIDTH the maximum width of the terminal --maxColumnWidth=MAXCOLWIDTH the maximum width to use when displaying columns --silent=[true/false] be more silent --report=[true/false] show number of rows and execution time after query execution --autosave=[true/false] automatically save preferences --outputformat=[table/vertical/csv2/tsv2/dsv/csv/tsv] format mode for result display Note that csv, and tsv are deprecated - use csv2, tsv2 instead --incremental=[true/false] Defaults to false. When set to false, the entire result set is fetched and buffered before being displayed, yielding optimal display column sizing. When set to true, result rows are displayed immediately as they are fetched, yielding lower latency and memory usage at the price of extra display column padding. Setting --incremental=true is recommended if you encounter an OutOfMemory on the client side (due to the fetched result set size being large). Only applicable if --outputformat=table. --incrementalBufferRows=NUMROWS the number of rows to buffer when printing rows on stdout, defaults to 1000; only applicable if --incremental=true and --outputformat=table --truncateTable=[true/false] truncate table column when it exceeds length --delimiterForDSV=DELIMITER specify the delimiter for delimiter-separated values output format (default: |) --isolation=LEVEL set the transaction isolation level --nullemptystring=[true/false] set to true to get historic behavior of printing null as empty string --maxHistoryRows=MAXHISTORYROWS The maximum number of rows to store beeline history. --delimiter=DELIMITER set the query delimiter; multi-char delimiters are allowed, but quotation marks, slashes, and -- are not allowed; defaults to ; --convertBinaryArrayToString=[true/false] display binary column data as string or as byte array --getUrlsFromBeelineSite Print all urls from beeline-site.xml, if it is present in the classpath --help display this message Example: 1. Connect using simple authentication to HiveServer2 on localhost:10000 $ beeline -u jdbc:hive2://localhost:10000 username password 2. Connect using simple authentication to HiveServer2 on hs.local:10000 using -n for username and -p for password $ beeline -n username -p password -u jdbc:hive2://hs2.local:10012 3. Connect using Kerberos authentication with hive/localhost@mydomain.com as HiveServer2 principal $ beeline -u "jdbc:hive2://hs2.local:10013/default;principal=hive/localhost@mydomain.com" 4. Connect using SSL connection to HiveServer2 on localhost at 10000 $ beeline "jdbc:hive2://localhost:10000/default;ssl=true;sslTrustStore=/usr/local/truststore;trustStorePassword=mytruststorepassword" 5. Connect using LDAP authentication $ beeline -u jdbc:hive2://hs2.local:10013/default <ldap-username> <ldap-password> ... View more

Use the below Query to fetch the table location from HMS .   select "DBS"."NAME" as DB_NAME, "TBLS"."TBL_NAME", "SDS"."LOCATION" from "DBS" join "TBLS" on "DBS"."DB_ID" = "TBLS"."DB_ID" AND "TBLS"."TBL_TYPE" != 'VIRTUAL_VIEW' join "SDS" on "TBLS"."SD_ID" = "SDS"."SD_ID";     To query the same from hive, Would recommend to use JDBC Storage Handler.  In CDP by default in sysdb this tables has been created. you can use the same.  Query  select dbs.name as db_name , tbls.tbl_name , sds.location from dbs join tbls on dbs.db_id = tbls.db_id and tbls.tbl_type != 'VIRTUAL_VIEW' join sds on tbls.sd_id = sds.sd_id; ... View more