메뉴 건너뛰기

Bigdata, Semantic IoT, Hadoop, NoSQL

Bigdata, Hadoop ecosystem, Semantic IoT등의 프로젝트를 진행중에 습득한 내용을 정리하는 곳입니다.
필요한 분을 위해서 공개하고 있습니다. 문의사항은 gooper@gooper.com로 메일을 보내주세요.


------ 전체 테이블 목록
select a.owner as tbl_owner, a.owner_type as tbl_owner_type,
a.tbl_name, a.tbl_type, B.DB_LOCATION_URI, b.name as db_name from  HIVE.TBLS a, HIVE.DBS b where a.db_id=B.DB_id;


------전체 코디네이터 목록
select a.id, a.name, dbms_lob.substr(a.description, 10000,1), B.USERNAME from HUE.DESKTOP_DOCUMENT2 a, hue.auth_user b
where a.owner_id=B.ID and type='oozie-coordinator2' and is_history=0 and is_trashed=0;


------전체 WF 목록
select a.name, dbms_lob.substr(a.description, 10000,1), B.USERNAME from HUE.DESKTOP_DOCUMENT2 a, hue.auth_user b
where a.owner_id=B.ID and type='oozie-workflow2' and is_history=0 and is_trashed=0;


---전체 코디네이터및 WF목록
select a.id, a.name, dbms_lob.substr(a.description, 10000,1), B.USERNAME, a.type, A.LAST_MODIFIED from HUE.DESKTOP_DOCUMENT2 a, hue.auth_user b
where a.owner_id=B.ID and type in('oozie-workflow2','oozie-coordinator2') and is_history=0 and is_trashed=0;


-----from_document2_id기준 전체

select b.lvl, a.id, (select k.username from hue.auth_user k where k.id=a.owner_id) as username, a.last_modified,
a.name, dbms_lob.substr(a.description, 10000,1) as remark, a.type
, (select c.name from hue.desktop_document2 c where c.id=b.from_document2_id) as from_work, b.from_document2_id as from_id
, (select d.name from hue.desktop_document2 d where d.id=b.to_document2_id) as to_work
, (select dbms_lob.substr(e.description, 10000,1) from hue.desktop_document2 e where e.id=b.to_document2_id) as to_work_remark
, b.to_document2_id as to_id
,b.isloop
from hue.desktop_document2 a,
(
  select level as lvl, id, from_document2_id, to_document2_id, connect_by_iscycle isloop from HUE.DESKTOP_DOCUMENT2_DEPENDENCIES
   -- start with from_document2_id=67292 -- 코디네이터
  connect by nocycle prior to_document2_id = from_document2_id
) b
where a.id=b.from_document2_id and is_history=0 and is_trashed=0 and type in ('oozie-coordinator2','oozie-workflow2');


-------코디네이터를 기준으로 코디네이터와 WF 구조 목록.
select b.lvl, a.id, (select k.username from hue.auth_user k where k.id=a.owner_id) as username, a.last_modified,
a.name, dbms_lob.substr(a.description, 10000,1) as remark, a.type
, (select c.name from hue.desktop_document2 c where c.id=b.from_document2_id) as from_work, b.from_document2_id as from_id
, (select d.name from hue.desktop_document2 d where d.id=b.to_document2_id) as to_work
, (select dbms_lob.substr(e.description, 10000,1) from hue.desktop_document2 e where e.id=b.to_document2_id) as to_work_remark
, b.to_document2_id as to_id
,b.isloop
from hue.desktop_document2 a,
(
  select level as lvl, id, from_document2_id, to_document2_id, connect_by_iscycle isloop from HUE.DESKTOP_DOCUMENT2_DEPENDENCIES
   -- start with from_document2_id=67292 -- 코디네이터
   start with from_document2_id in (select id from HUE.DESKTOP_DOCUMENT2
       where type='oozie-coordinator2' and is_history=0 and is_trashed=0)
  connect by nocycle prior to_document2_id = from_document2_id
) b
where a.id=b.from_document2_id and is_history=0 and is_trashed=0 and type in ('oozie-coordinator2','oozie-workflow2');



------HUE.DESKTOP_DOCUMENT2_DEPENDENCIES에는 없고 HUE.DESKTOP_DOCUMENT2에만 있는 코디네이터 혹은 워크플로우 목록
select a.*, b.username from HUE.DESKTOP_DOCUMENT2 a, hue.auth_user b
       where type in('oozie-coordinator2','oozie-workflow2') and is_history=0 and is_trashed=0
       and a.id not in ( select from_document2_id from HUE.DESKTOP_DOCUMENT2_DEPENDENCIES
                       union
                       select to_document2_id from HUE.DESKTOP_DOCUMENT2_DEPENDENCIES
                      )
       and a.owner_id=B.ID;              

번호 제목 글쓴이 날짜 조회 수
149 javax.net.ssl.SSLHanshakeException: SSLHandshakeException invoking https://mainCluster.gooper.com:7183/api/v1/users: sun.security.validator.ValidatorException: No trusted certificate found gooper 2022.06.29 10
148 Cloudera Manager의 Java Heap Size변경하는 방법 gooper 2022.06.27 2
147 [Sentry] sentry메타 DB를 이용하여 테이블에 매핑되어 있는 role명칭 찾는 방법. gooper 2022.06.22 0
146 Authorization within Hadoop Projects gooper 2022.06.13 7
145 CDP에서 AD와 Kerberos를 활용하여 인증 환경을 구축하는 3가지 방법 gooper 2022.06.10 12
144 [AD(LADP)] CDP1.7에서 AD및 Kerberos를 연동해도 각 노드에 os account, os group은 생성되어야 한다. gooper 2022.06.10 7
143 HDFS 파일및 디렉토리 생성시 생성방법에 따라 권한이 다르게 부여된다. gooper 2022.05.30 18
142 [Cloudera Agent] Metadata-Plugin throttling_logger INFO (713 skipped) Unable to send data to nav server. Will try again. gooper 2022.05.16 1
141 "bad handshake: Error([('SSL routines', 'ssl3_get_server_certificate', 'certificate verify failed')])" 오류는 CA인증을 하지 못해서 발생함 총관리자 2022.05.13 7
140 [TLS/SSL]Cloudera CDH6.3.4기준 Hue TLS설정 항목 총관리자 2022.05.13 8
139 [HIVESERVER2]프로세스의 thread및 stack trace를 덤프하는 방법(pstack, jstack) 총관리자 2022.05.11 9
138 [Hue]Hue의 메타정보를 담고 있는 desktop_document테이블과 desktop_document2의 관계 총관리자 2022.05.09 2
» hue메타 정보를 저장(oracle DB)하는 내부 테이블을 이용하여 전체 테이블목록, 전체 코디네이터 목록, 코디네이터기준 workflow구조를 추출하는 쿼리문 총관리자 2022.04.01 18
136 HDFS에서 quota 설정 방법및 확인 방법 총관리자 2022.03.30 7
135 [application수행 로그]Failed to read the application application_123456789012_123456시 조치 방법 총관리자 2022.03.21 9
134 Hue impala에서 query결과를 HDFS 파일로 export시 AuthorizationException: User 'gooper1234' does not have privileges to access: db명.query_impala_123456 총관리자 2022.03.17 24
133 [TLS]pkcs12형식의 인증서 생성및 jks형식 인증서 생성 커맨드 예시 총관리자 2022.03.15 8
132 [TLS]TLS용 사설 인증서 변경 혹은 신규 지정시 No trusted certificate found 오류 발생시 확인및 조치사항 총관리자 2022.03.15 10
131 Hadoop NFS gateway기동시 Cannot connect to port 2049 오류 발생시 확인/조치 총관리자 2022.03.02 17
130 Oracle RAC 구성된 DB서버에 대한 컴포넌트별 설정 방법 총관리자 2022.02.12 9

A personal place to organize information learned during the development of such Hadoop, Hive, Hbase, Semantic IoT, etc.
We are open to the required minutes. Please send inquiries to gooper@gooper.com.

위로