메뉴 건너뛰기

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;              

번호 제목 글쓴이 날짜 조회 수
658 restaurant-controller,에서 등록 예시 총관리자 2022.04.30 33
657 Could not authenticate, GSSException: No valid credentials provided (Mechanism level: Failed to find any kerberos tgt) 총관리자 2022.04.28 27
656 [oracle]10자리 timestamp값을 날짜로 변환하는 방법 총관리자 2022.04.14 40
655 [hive] hive.tbls테이블의 owner컬럼값은 hadoop.security.auth_to_local에 의해서 filtering된다. 총관리자 2022.04.14 55
654 collection생성혹은 collection조회시 Plugin init failure for [schema.xml] fieldType "pdate": Error loading class 'solr.IntField' 오류 조치사항 총관리자 2022.04.07 107
» hue메타 정보를 저장(oracle DB)하는 내부 테이블을 이용하여 전체 테이블목록, 전체 코디네이터 목록, 코디네이터기준 workflow구조를 추출하는 쿼리문 총관리자 2022.04.01 48
652 HDFS에서 quota 설정 방법및 확인 방법 총관리자 2022.03.30 50
651 [oozie]Oozie WF수행시 단계별 ID넘버링 비교/설명 총관리자 2022.03.23 16
650 [application수행 로그]Failed to read the application application_123456789012_123456시 조치 방법 총관리자 2022.03.21 46
649 Hue impala에서 query결과를 HDFS 파일로 export시 AuthorizationException: User 'gooper1234' does not have privileges to access: db명.query_impala_123456 총관리자 2022.03.17 212
648 [TLS]pkcs12형식의 인증서 생성및 jks형식 인증서 생성 커맨드 예시 총관리자 2022.03.15 120
647 [TLS]TLS용 사설 인증서 변경 혹은 신규 지정시 No trusted certificate found 오류 발생시 확인및 조치사항 총관리자 2022.03.15 60
646 [CentOS 7.4]Hadoop NFS gateway기동시 Cannot connect to port 2049 오류 발생시 확인/조치 총관리자 2022.03.02 74
645 Oracle RAC 구성된 DB서버에 대한 컴포넌트별 설정 방법 총관리자 2022.02.12 27
644 service name방식의 oracle을 메타정보 저장소로 사용할때 Hue Configuration설정하는 방법 총관리자 2022.02.12 15
643 oracle 접속 방식에 따른 --connect 지정 방법 총관리자 2022.02.11 23
642 [vue storefrontui]외부 API통합하기 참고 문서 총관리자 2022.02.09 7
641 vuestorefrontui.io를 이용한 front end project 생성하기 총관리자 2022.02.06 23
640 eclipse editor 설정방법 총관리자 2022.02.01 8
639 windows10 pro에서 microservice pattern책의 예제를 kubernetes에서 기동하는 방법 총관리자 2022.01.30 17

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.

위로